SQL Server：加入几个表

I need to do a query with the following criteria. I have table1, table2 and table3. I need to check that each of table2 and table3 are subsets of table1.

我需要使用以下条件进行查询。我有table1,table2和table3。我需要检查table2和table3中的每一个是table1的子集。

All of the content of table1 should be present in table2 and/or table3. All the content of table1 could be present in table2 alone and sometimes all the content of table1 should be present in table3 only. Other times table1 could be the content of table2 and table3. Not everything in table2 should match what is in table3.

table1的所有内容都应该出现在table2和/或table3中。 table1的所有内容都可以单独存在于table2中,有时table1的所有内容只应出现在table3中。其他时候table1可以是table2和table3的内容。并非table2中的所有内容都与table3中的内容相匹配。

I want the query to return what is present in table1 and not find in table2 nor in table3. In addition, it should return what is present in table2 and table3 and not present in table1.

我希望查询返回table1中的内容,而不是在table2和table3中找到。此外,它应该返回table2和table3中的内容,而不是table1中的内容。

I have tried the below but it doesnt seem working. Do you have any idea how this could be solved?

我尝试了以下但它似乎没有工作。你知道如何解决这个问题吗?

Select 
    dr1.col1, dr1.col2 
from 
    table1 dr1 
left outer join 
    (Select c.col1, col2 from table2) dr2 on dr1.col1 = dr2.col1 
                                          and dr1.col2 = dr2.col2 
left outer join 
    (Select col1, col2, col3 from tabl3 ) dr3 on dr1.col1 = dr3.col1 
                                              and dr1.col2 = dr3.col2 
                                              and dr1.col3 = dr3.col3 
where 
    dr1.col1 is NULL or dr2.col2 is NULL

3 个解决方案

#1

Use FULL JOIN with UNION:

与UNION一起使用FULL JOIN:

DECLARE @t1 TABLE ( ID INT )
DECLARE @t2 TABLE ( ID INT )
DECLARE @t3 TABLE ( ID INT )


INSERT  INTO @t1
VALUES  ( 1 ),( 2 ),( 3 ),( 4 ),( 5 ),( 6 ),( 7 ),( 8 ),( 9 )

INSERT  INTO @t2
VALUES  ( 1 ),( 2 ),( 3 ),( 4 )

INSERT  INTO @t3
VALUES  ( 4 ),( 5 ),( 6 ),( 7 ),( 8 ),( 10 )


SELECT  ISNULL(t.ID, tt.ID) AS ID ,
        CASE WHEN t.ID IS NULL THEN 'Missing in Table 1'
             ELSE 'Missing in Table 2, 3'
        END AS MissingValue
FROM    @t1 t
        FULL JOIN ( SELECT  ID FROM    @t2
                    UNION
                    SELECT  ID FROM    @t3
                  ) tt ON t.ID = tt.ID
WHERE   t.ID IS NULL OR tt.ID IS NULL

Output:

ID  MissingValue
9   Missing in Table 2, 3
10  Missing in Table 1

#2

In addition, it should return what is present in table2 and table3 and not present in table1.

此外,它应该返回table2和table3中的内容,而不是table1中的内容。

How do you want to differ between the two, when you return it all in one query?

当你在一个查询中全部返回时,你想如何区分两者?

To the original question, try this:

对于原始问题,请尝试以下方法:

SELECT dr1.col1, dr1.col2
FROM table1 dr1
WHERE NOT EXISTS (
  SELECT 1
  FROM table2 dr2
  WHERE dr2.col1 = dr1.col1
  AND dr2.col2 = dr1.col2)
AND NOT EXISTS (
  SELECT 1
  FROM table3 dr3
  WHERE dr3.col1 = dr1.col1
  AND dr3.col2 = dr1.col2)

That way you get all data in table 1 that is not in table 2 or table 3.

这样,您可以获得表1中不在表2或表3中的所有数据。

#3

To find the records present in table1 and not present in table2 and table3, you could use the following query. I am assuming that there is a primary key field on both table2 and table3, which I refer to as ID. I have also eliminated the subselect.

要查找table1中存在且不存在于table2和table3中的记录,可以使用以下查询。我假设table2和table3上都有一个主键字段,我将其称为ID。我也删除了子选择。

SELECT dr1.col1, dr1.col2 from table1 dr1 
    LEFT OUTER JOIN table2 dr2 
        ON dr1.col1 = dr2.col1 and dr1.col2 = dr2.col2 
    LEFT OUTER JOIN table3 dr3
        ON dr1.col1 = dr3.col1 and dr1.col2 = dr3.col2 and dr1.col3 = dr3.col3  
    WHERE dr2.ID is NULL AND dr3.ID is NULL

If you want to know what is present in table2 and table3 but not in table1, you cannot select from table1. Instead you could select from table2, joined to table3 and left outer join table 1:

如果您想知道table2和table3中的内容,而不是table1中的内容,则无法从table1中进行选择。相反,你可以从table2中选择,加入table3和左外连接表1:

SELECT dr2.col1, dr2.col2 from table2 dr2
    INNER JOIN  table3 dr3
        ON dr2.col1 = dr3.col1 and dr2.col2 = dr3.col2
    LEFT OUTER JOIN table1 dr1 
        ON dr1.col1 = dr2.col1 and dr1.col2 = dr2.col2 
    WHERE dr1.ID is NULL

#1