Home > Software design >  how do I properly create an sql query that joins four tables with a common key
how do I properly create an sql query that joins four tables with a common key

Time:05-02

I have four tables, that have a key in common. three of the four tables are small subsets of the fourth,(master). I want to join the tables such that only the output only contains records from the master table, that are on any of other fourth:

as an example: enter image description here

My end result should look like this:

enter image description here

My problem is that joins I'm using, are giving me only the records that are common to all tables. or records that are common to only one of the tables and the master.

Any help on formulating the correct join would be awesome!

CodePudding user response:

Three left joins will produce the result you want. For example:

select a.*, b.color, c.size, d.weight
from a
left join b on b.id = a.id
left join c on c.id = a.id
left join d on d.id = a.id
where b.id is not null or c.id is not null or d.id is not null

EDIT: Added WHERE clause above as requested.

  • Related