Valkyrurr Valkyrurr - 6 months ago 10
SQL Question

SELECT Query grouping/merging for non-DISTINCT result set

---------- ---------- ---------- ----------
| t1 | | t2 | | t3 | | t4 |
---------- ---------- ---------- ----------
| id | | id | | id | | t1_id |
| foo | | bar | | foobar | | t2_id |
---------- ---------- ---------- | t3_id |
----------


I have this set of tables, and with this particular query:


SELECT foo, bar, foobar FROM t4

LEFT JOIN t1 ON t1.id = t4.t1_id

LEFT JOIN t2 ON t2.id = t4.t2_id

LEFT JOIN t3 ON t3.id = t4.t3_id;


I could then come up with this particular result set:

------------------------------ -------------------------------
| foo | bar | foobar | => | foo | bar | foobar |
------------------------------ -------------------------------
| x | y | a | | x | y | a |
| x | y | b | | | | b |
| x | y | c | | | | c |
------------------------------ -------------------------------


QUESTION IS: How can I format my query to somehow group those columns with similar entries but not those that are not? Is this even possible in
MySQL
? I'm outputting this through
PHP
, so should I let
PHP
handle its formatting?

Same question if it should be handled by
PHP
.

Answer

You should let PHP handle the formatting. In general, SQL result sets (and tables) represent unordered sets. Your formatting has a particular ordering in mind.

It might help to put the values in one row, with the third column as a "list":

SELECT foo, bar, GROUP_CONCAT(foobar) as foobars
FROM t4 LEFT JOIN
     t1
     ON t1.id = t4.t1_id LEFT JOIN
     t2 
     ON t2.id = t4.t2_id LEFT JOIN
     t3
     ON t3.id = t4.t3_id
GROUP BY foo, bar;

That said, it is possible to do what you want in MySQL (using variables, for instance), but PHP is the better place to do the formatting.