CODEWITHSUNDEEP
mysqljoin
Atul
Atul

Reputation: 4350

Joining table to two other tables in single query

I am getting rows from table_a by joining it to table_b in this way:

select ta.* from table_a as ta 
inner join table_b as tb on ta.first_column=tb.first_column 
where tb.second_column='myvalue';

And,

I am getting rows from table_a by joining it to table_c in this way:

select ta.* from table_a as ta 
inner join table_c as tc on ta.first_column=tc.first_column 
where tc.second_column='myvalue';

How can I form a single query to get all the rows of the above queries (i.e. union of the results from the above queries)?

I do not want to do an Union per se, but rather do it by forming a query which joins table_a to table_b and table_c.

Upvotes: 1

Views: 40

Answers (1)

Laurentiu L.
Laurentiu L.

Reputation: 6686

You can do multiple joins in the same query.

select ta.*,tb.*,tc.* from table_a as ta 
inner join table_b as tb on ta.first_column=tb.first_column 
inner join table_c as tc on ta.first_column=tc.first_column 
where tc.second_column='myvalue' and tb.second_column='myvalue';

If you want all the records for which the second_column is 'myvalue' for both tables(b and c) at the same time you will use the query above with the and conjunction in the where clause.

Otherwise, if you wantall the records for which the second_column is 'myvalue' for at least 1 of he tables(b or c) you will replace the and cojunction with or.

Update If you don't want just the rows for which first_column was common in table_b and table_c as you described try this:

select distinct ta.* from table_a as ta 
left join table_b as tb on ta.first_column=tb.first_column 
left join table_c as tc on ta.first_column=tc.first_column 
where tc.second_column='myvalue' or tb.second_column='myvalue';

Upvotes: 2

Related Questions