How can I join 4 tables-CodePudding

Please help to modify the below code. I want to join these two select queries such that I can get a single output with records from two diff tables ? I have 4 tables,

table_a has the user id that I have to use to search

table_b1 has the foreign key for table_c which has the name that I want to get

table_b2 also has the foreign key for table_c which has the second name that I want to get too.

How can I combine below query in a single output?

my code

select c.name from table_a a 
join table_b1 b1 on a.id=b1.id
join table_c c on b1.pri_id=c.id where a.user='abc'

select c.name from table_a a 
join table_b2 b2 on a.id=b2.id
join table_c c on b2.pri_id=c.id where a.user='abc'

CodePudding user response：

May be you can use UnionAll here like this,

select c.name from table_a a 
join table_b1 b1 on a.id=b1.id
join table_c c on b1.pri_id=c.id where a.user='abc'

union all

select c.name from table_a a
join table_b2 b2 on a.id=b2.id
join table_c c on b2.pri_id=c.id where a.user='abc'

CodePudding user response：

So you want to extract the names information in table_c for tables table_b1 and table_b2, which also join to table_a by the id column. You'll need to add table_c twice to your query to retrieve name from table_b1 and name from table_b2, because the conditions to retrieve the name are different.

If you only want names when there is information in table_b1 AND table_b2 (and in table_c for each of the b tables), the query will be:

SELECT tc1.name name_b1, tc2.name name_b2
FROM table_a ta
    JOIN table_b1 tb1 ON ta.id = tb1.id
    JOIN table_b2 tb2 ON ta.id = tb2.id
    JOIN table_c tc1 ON tb1.pri_id = tc1.id
    JOIN table_c tc2 ON tb2.pri_id = tc2.id
WHERE ta.user = 'abc'

If there might not always be information for user in table_b1 or table_b2, but you want to retrieve information anyway, getting NULL when there isn't a name on one or both of the b tables, you'll need left joins:

SELECT tc1.name name_b1, tc2.name name_b2
FROM table_a ta
    LEFT JOIN table_b1 tb1 ON ta.id = tb1.id
    LEFT JOIN table_b2 tb2 ON ta.id = tb2.id
    LEFT JOIN table_c tc1 ON tb1.pri_id = tc1.id
    LEFT JOIN table_c tc2 ON tb2.pri_id = tc2.id
WHERE ta.user = 'abc'

CodePudding user response：

Something like this?

select c.name from table_a a 
join table_b1 b1 on a.id=b1.id
join table_c c on b1.pri_id=c.id where a.user='abc'
join table_b2 b2 on a.id=b2.id
join table_c c2 on b2.pri_id=c2.id