MySQL: How do I add a string on row X2 with ID Y1 in column Z1 to row X1 with ID Y1 to column Z1?
TABLE A
Row IdA ValueA
1 1 ABCD
2 2 EFGH
3 开发者_如何学编程3 IJKL
TABLE B
Row IdB ValueB
1 1 QWER
2 2 TYUI
3 3 OPAS
CONNECTOR X
Row IdA IdB
1 1 1
2 1 2
3 2 3
I want the output to display:
OUTPUT
Value A --- ValueB(1), ValueB(2)
ABCD --- QWER, TYUI
So, basically, every time there's a doublet in the connector table's IdA column, those two (or more) entries merge the strings in the Value field for my output.
Is this even doable with a MySQL query, or do I -have- to resort to sorting through the whole database with a PHP array? I'd rather like to avoid that, if at all possible!
I've looked at the various JOINs to no avail and thought about using a GROUP BY and COUNT(DISTINCT ...) query, but it just seems a very inelegant way to go about it. Suggestions are welcome!
SELECT a.ValueA, GROUP_CONCAT( b.ValueB SEPARATOR ', ' ) AS ValuesB
FROM connector c
JOIN tblA a ON c.IdA = a.IdA
JOIN tblB b ON c.IdB = b.IdB
GROUP BY a.IdA
Will give you results:
+--------+------------+
| ValueA | ValuesB |
+--------+------------+
| ABCD | QWER, TYUI |
| EFGH | OPAS |
+--------+------------+
精彩评论