MySQL multiple variables in LEFT JOIN

Question

I have table dates as following

"dates"  "one" "two"  "three" "four"
date1    id1    id2    id3    id4
date2    id3    id1    id4    id2

and a second table id2name

"ids"  "names"
id1    name1
id2    name2
id3    name3
id4    name4

I have a mental block trying to write

SELECT * FROM dates WHERE `date`='$date' LEFT JOIN `id2name` ON ...

What clause do I put in ON to get names instead of IDs?

Answer 1

Your model looks denormalized, but this should get you started:

SELECT
  d.`date`,
  i1.names AS name1,
  i2.names AS name2,
  ...
FROM dates d
LEFT JOIN `id2name` i1 ON ( i1.ids = d.one )
LEFT JOIN `id2name` i2 ON ( i2.ids = d.two )
...
WHERE `date`='$date'

Answer 2

As far as i know you can only join on one id at a time so you'll have to do a join on each column that has these id's