CODEWITHSUNDEEP
sqlsql-serverjoin
mzbib
mzbib

Reputation: 119

SQL Server : Join on several tables

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.

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.

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.

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

Upvotes: 0

Views: 63

Answers (3)

Thomas F.
Thomas F.

Reputation: 737

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.

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:

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

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Use FULL JOIN with UNION:

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

Upvotes: 1

Alexander Mills
Alexander Mills

Reputation: 277

In addition, it should return what is present in table2 and table3 and not present in 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.

Upvotes: 0

Related Questions