Birrel Birrel - 7 months ago 10
SQL Question

PHP, MySQL - Get multiple, identifiable, rows returned from a single JOIN?

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 a single value from
val3
,
val4
or
val5
from the first table. The second table is only included in the question for completeness, and is not the issue.

The query I have is the following:

$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 this works a treat, exactly as expected.

BUT!

I don't only need
t3col1
for
t1.val3 = t3.col2
. I also need it for
t1.val4 = t3.col2
and
t1.val5 = t3.col2


I can construct the line:

JOIN table3 t3 ON (t1.val3 = t3.col2 OR t1.val4 = t3.col2 OR t1.val5 = t3.col2)


But then I don't know which value was used to get
t3col1
. And it only returns a single value, instead of all three.

See my problem!?

I'd 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?

clarification

IF
val1
,
val2
and
val3
all contain values, I would like a single row returned, with all three matches from table3. NOT three separate rows, that are all identical except for the match column.

This SQLFiddle is an example of what I DO NOT want. Notice that everything is identical, except for the
t3col1
columns. This should be a single row, with all the
t3col1
values.

/clarification

I reckon I can JOIN the table three separate 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 do I lose performance doing it this way (i.e. 4 JOINS), opposed to how I am trying to do it, with just the 2?

Bonus!

Sometimes
val3
,
val4
and/or
val5
will be empty (but
col2
is never empty). Do all the table3 JOINs need to be
LEFT JOIN
s, for those cases with empty values?

EDIT 1

As per your requests, I've set it up in SQLFiddle, but the service is hit-and-miss.

You can find it here

EDIT 2

I tried joining table3 multiple 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 ended up getting more than 90.

val1
will always have a value, and whether
val2
and
val3
are populated is different every time. But when all three have values, I don't want three separate returns for it. Instead, I just want the one return with all three matches in it.

Answer

Below query will give you the data as you wanted:

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,
 t3.col2 AS t3col2,
 CASE WHEN t1.val3 = t3.col2 THEN t3.col1 ELSE '' END AS 't3col11',
 CASE WHEN t1.val4 = t3.col2 THEN t3.col1 ELSE '' END AS 't3col21',
 CASE WHEN t1.val5 = t3.col2 THEN t3.col1 ELSE '' END AS 't3col31',
 CASE WHEN t1.val3 = t3.col2 THEN 't1col3'
 ELSE CASE WHEN t1.val4 = t3.col2 THEN 't1col4' ELSE 't1col5'
 END END AS ColumnName
FROM table1 t1
  JOIN table2 t2 ON t1.val2 = t2.som2
  JOIN table3 t3 ON (t1.val3 = t3.col2 OR t1.val4 = t3.col2 OR t1.val5 = t3.col2)
GROUP BY t2som1,t1v1, t1v2, t1v3, t1v4, t1v5

The last column that is 'ColumnName' tells you which t1 column was the one that matches the value.

Edit - Using left join

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,
    t3.col1 AS t3col1,
    t4.col1 AS t3col2,
    t5.col1 AS t3col3
FROM table1 AS t1
LEFT JOIN table2 t2 ON t1.val2 = t2.som2
LEFT JOIN table3 t3 ON t1.val3 = t3.col2
LEFT JOIN table3 t4 ON t1.val4 = t4.col2
LEFT JOIN table3 t5 ON t1.val5 = t5.col2