MySQL Conditional Left Joins
Ok, so the title is a bit misleading… I am not going to show you necessarily how to only LEFT JOIN a table based on a condition, but I will show you how to get the same results as you would expect.
First, you may be asking…. why would I ever need to? Well, let ’s say you have 3 tables. Table One is acting as your primary index relation table. Then, based on the info IN that table, you need to join either table Two or table Three. How would you do it?
SELECT `one`.first_name, `one`.male_female,
IF(`one`.male_female=’boy’,boyTable.meaning,girlTable.meaning) AS name_meaning
FROM `one`
LEFT JOIN `two` AS boyTable ON boyTable.first_name=`one`.first_name
LEFT JOIN `three` AS girlTable ON girlTable.first_name=`one`.first_name
This will let you, for example, select the meaning of someones name if the meanings where in two different tables based on gender. This is of course, is just an example, and this particular situation would be handled better by just setting a flag in a singular table that specifies male of female, but I needed to come up with some way illustrate the concept, so her you are!
hope this helps someone