CODEWITHSUNDEEP
mysqljoin
brian wilson
brian wilson

Reputation: 495

mysql joining three tables with if statements

I've got a query that's working

SELECT *
FROM the_index AS t1
LEFT JOIN table_2 AS t2 ON (t1.activity_id = t2.id) 
LEFT JOIN table_3 AS t3 ON (t1.activity_id = t3.id)
ORDER BY t1.id;

that works fine but i want to modify it with some if statements..

SELECT *
FROM the_index AS t1
LEFT JOIN table_2 AS t2 ON (t1.activity_id = t2.id)     <---- If t1.event = '2'
LEFT JOIN table_3 AS t3 ON (t1.activity_id = t3.id)     <---- If t1.event = '3'
ORDER BY t1.id;

Upvotes: 0

Views: 78

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65274

Do you mean

SELECT *
FROM the_index AS t1
LEFT JOIN table_2 AS t2 ON (t1.activity_id = t2.id AND t1.event = '2')
LEFT JOIN table_3 AS t3 ON (t1.activity_id = t3.id AND t1.event = '3')
ORDER BY t1.id;

Caveat: This goes explicitly against MySQL recommendations not to put WHERE-like clauses into a join.

Upvotes: 0

Dercs&#225;r
Dercs&#225;r

Reputation: 1694

simply use this:

LEFT JOIN table_2 AS t2 ON (t1.activity_id = t2.id AND t1.event = '2')
LEFT JOIN table_3 AS t3 ON (t1.activity_id = t3.id AND t1.event = '3')

Note, that this only works as long as LEFT JOIN is used. Won't work for INNER JOINs.

Upvotes: 1

Related Questions