Loading

Join on 4 table

Join on 4 table

Sonali Avhad

5y799Learn iOS Programming

There are 4 table A,B,C and D.

There are all records in A. and some of records in B ,some in C and some in D.

I want to select those records from A and which are not in B,C and D.

Please tell me sql join query .

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
what you want is A-(B+C+D) so your query should be
select * from table1
except
(SELECT *
FROM table2
UNION
SELECT *
FROM table3
UNION
SELECT *
FROM table4)
Nitin Sontakke5y ago
The accepted answer is fine to give in an interview as it technically correct.

In real life, however, rarely it is as simple as all tables are having exact same structure and need comparision.

When you are faced with that situation, assuming there is a key available for comparision, which it should as it makes no sense to compare unrelated tables, you can use something as shown below:
select t1.[Id], t1.[Name]
  ,case when t2.[Id] is null then 'Yes' else 'No' end [Missing from B]
  ,case when t3.[Id] is null then 'Yes' else 'No' end [Missing from C]
  ,case when t4.[Id] is null then 'Yes' else 'No' end [Missing from D]
from [dbo].[Table1] t1
left outer join [dbo].[Table2] t2 on t1.[Id] = t2.[Id]
left outer join [dbo].[Table3] t3 on t1.[Id] = t3.[Id]
left outer join [dbo].[Table4] t4 on t1.[Id] = t4.[Id]
Mageshwaran R5y ago
Hi Sonali,
Please try this code,
SELECT A.Column1,A.Column2,A.Column3
FROM A
INNER JOIN B ON B.id = A.id
INNER JOIN C ON C.id = A.id;
INNER JOIN D ON D.id = A.id;