PHP, MySQL - Get multiple, identifiable, rows returned from a single JOIN?
<p>I have three tables, something like:</p>

<pre><code>*table1*

id | val1 | val2 | val3 | val4 | val5
 1 |  ... |  ... |  ... |  ... |  ...
 2 |  ... |  ... |  ... |  ... |  ...
 3 |  ... |  ... |  ... |  ... |  ...
... etc
</code></pre>

<p>and</p>

<pre><code>*table2*

id | som1 | som2 
 1 |  ... |  ... 
 2 |  ... |  ... 
 3 |  ... |  ... 
... etc
</code></pre>

<p>and </p>

<pre><code>*table3*

id | col1 | col2 
 1 |  ... |  ... 
 2 |  ... |  ... 
 3 |  ... |  ... 
... etc
</code></pre>

<p>where <code>col2</code> in the third table will <em>always</em> contain a single value from <code>val3</code>, <code>val4</code> or <code>val5</code> from the first table. The second table is only included in the question for completeness, and is not the issue.</p>

<p>The query I have is the following:</p>

<pre><code>$db = new PDO(...);
$stmt = $db-&gt;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-&gt;execute(array('input'=&gt;$inputVal));
$result = $stmt-&gt;fetchAll();
</code></pre>

<p>And this works a treat, exactly as expected.</p>

<p>BUT!</p>

<p>I don't <em>only</em> need <code>t3col1</code> for <code>t1.val3 = t3.col2</code>. I also need it for <code>t1.val4 = t3.col2</code> and <code>t1.val5 = t3.col2</code></p>

<p>I can construct the line:</p>

<pre><code>JOIN table3 t3 ON (t1.val3 = t3.col2 OR t1.val4 = t3.col2 OR t1.val5 = t3.col2)
</code></pre>

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

<p>See my problem!?</p>

<p>I'd like to have</p>

<pre><code>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
</code></pre>

<p>Can this be done?</p>

<p><strong>clarification</strong></p>

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

<p><a href="http://sqlfiddle.com/#!9/72835/16" rel="nofollow">This SQLFiddle</a> is an example of what I <em>DO NOT</em> want. Notice that everything is identical, except for the <code>t3col1</code> columns. This should be a single row, with all the <code>t3col1</code> values.</p>

<p><strong>/clarification</strong></p>

<p>I reckon I can JOIN the table three separate times:</p>

<pre><code>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 (...)
</code></pre>

<p>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?</p>

<p><strong>Bonus!</strong></p>

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

<p><strong>EDIT 1</strong></p>

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

<p>You can find it <a href="http://sqlfiddle.com/#!9/72835/6" rel="nofollow">here</a></p>

<p><strong>EDIT 2</strong></p>

<p>I tried joining table3 multiple times:</p>

<pre><code>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 (...)
</code></pre>

<p>and ended up getting a row for each match. I should have had 7 results, and ended up getting more than 90.</p>

<p><code>val1</code> will always have a value, and whether <code>val2</code> and <code>val3</code> 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.</p>

<p><strong>EDIT 4 (The clarification was 3...)</strong></p>

<p>The <em>multiple JOINs</em> from EDIT 2 was what ended up working, thanks to Siraj's answer. The problem was that I used:</p>

<pre><code>JOIN table3 t3 ON t1.val3 = t3.col2 
JOIN table3 t4 ON t1.val4 = t3.col2 
JOIN table3 t5 ON t1.val5 = t3.col2
</code></pre>

<p>when I should have used:</p>

<pre><code>JOIN table3 t3 ON t1.val3 = t3.col2 
JOIN table3 t4 ON t1.val4 = t4.col2 
JOIN table3 t5 ON t1.val5 = t5.col2
</code></pre>

<p>Notice the difference? It's just the <code>t3.col2</code> after each equal sign - they need to reference the correct table! The rest all stays the same, and Bob's your auntie!</p>