I have three tables, something like:
*table1* id | val1 | val2 | val3 | val4 | val5 1 | ... | ... | ... | ... | ... 2 | ... | ... | ... | ... | ... 3 | ... | ... | ... | ... | ... ... etc
and
*table2* id | som1 | som2 1 | ... | ... 2 | ... | ... 3 | ... | ... ... etc
and
*table3* id | col1 | col2 1 | ... | ... 2 | ... | ... 3 | ... | ... ... etc
where col2 in the third table will always contain one value from val3 , val4 or val5 from the first table. The second table is included only in the question of completeness, and this is not a problem.
I have the following query:
$db = new PDO(...); $stmt = $db->prepare("SELECT t2.som1 AS t2som1, t1.val1 AS t1v1, t1.val2 AS t1v2, t1.val3 AS t1v3, t1.val4 AS t1v4, t1.val5 AS t1v5, t3.col1 AS t3col1 FROM table1 t1 JOIN table2 t2 ON t1.val2 = t2.som2 JOIN table3 t3 ON t1.val3 = t3.col2 WHERE (t1.val4=:input OR t1.val5=:input) AND (t1.val1=1 OR t1.val1=2)"); $stmt->execute(array('input'=>$inputVal)); $result = $stmt->fetchAll();
And it works as expected.
BUT!
I need not only t3col1 for t1.val3 = t3.col2 . I also need this for t1.val4 = t3.col2 and t1.val5 = t3.col2
I can build a line:
JOIN table3 t3 ON (t1.val3 = t3.col2 OR t1.val4 = t3.col2 OR t1.val5 = t3.col2)
But then I do not know what value was used to obtain t3col1 . And it returns only one value, not all three.
See my problem !?
I would like to have
SELECT t2.som1 AS t2som1, ... t3.col1 AS t3col1, // for condition t1.val3 = t3.col2 t3.col1 AS t3col2, // for condition t1.val4 = t3.col2 t3.col1 AS t3col3 // for condition t1.val5 = t3.col2
Can this be done?
lightening
IF val1 , val2 and val3 all contain values, I would like to return a single row with all three matches from table3. NOT three separate rows, they are all identical except for the matching column.
This SQLFiddle is an example of what I DO NOT want. Note that everything is identical except for the t3col1 columns. This should be a single line with all t3col1 values.
/ lightening
I believe that I can JOIN the table three times:
SELECT t2.som1 AS t2som1, ... t3.col1 AS t3col1 t4.col1 AS t3col2 t5.col1 AS t3col3 FROM table1 t1 JOIN table2 t2 ON t1.val2 = t2.som2 JOIN table3 t3 ON t1.val3 = t3.col2 // for condition t1.val3 = t3.col2 JOIN table3 t4 ON t1.val4 = t3.col2 // for condition t1.val4 = t3.col2 JOIN table3 t5 ON t1.val5 = t3.col2 // for condition t1.val5 = t3.col2 WHERE (...) AND (...)
but am I losing performance by doing it this way (i.e. 4 JOINS), against how I am trying to do this, only 2?
Bonus!
Sometimes val3 , val4 and / or val5 will be empty (but col2 will never be empty). Should all 3 JOINs tables be LEFT JOIN s, for those cases with empty values?
EDIT 1
According to your requests, I installed it in SQLFiddle, but the service was deleted and skipped.
You can find here
EDIT 2
I tried to connect table3 several times:
SELECT t2.som1 AS t2som1, ... t3.col1 AS t3col1 t4.col1 AS t3col2 t5.col1 AS t3col3 FROM table1 t1 JOIN table2 t2 ON t1.val2 = t2.som2 JOIN table3 t3 ON t1.val3 = t3.col2 // for condition t1.val3 = t3.col2 JOIN table3 t4 ON t1.val4 = t3.col2 // for condition t1.val4 = t3.col2 JOIN table3 t5 ON t1.val5 = t3.col2 // for condition t1.val5 = t3.col2 WHERE (...) AND (...)
and ended up getting a row for each match. I should have had 7 results, and I got more than 90.
val1 will always matter and whether val2 and val3 each time. But when all three matter, I don't want three separate returns for him. Instead, I just want one to return with all three matches in it.
EDIT 4 (Lightening was 3 ...)
Several JOINs from EDIT 2 were what ended up working, thanks to Siraj's answer. The problem was what I used:
JOIN table3 t3 ON t1.val3 = t3.col2 JOIN table3 t4 ON t1.val4 = t3.col2 JOIN table3 t5 ON t1.val5 = t3.col2
when I should have used:
JOIN table3 t3 ON t1.val3 = t3.col2 JOIN table3 t4 ON t1.val4 = t4.col2 JOIN table3 t5 ON t1.val5 = t5.col2
Pay attention to the difference? It's just t3.col2 after each equal sign - they need to refer to the correct table! The rest are all the same, and Bob is your aunt!