CODEWITHSUNDEEP
mysqlsqljoinone-to-many
topherg
topherg

Reputation: 4303

Creating a Join Query for a one-to-many table relationship

I have a table (Table D) that has a field that has another table (Table M) associated with it which contains a set of rows that reference a single row in table D.

Can (and if so, how) can I create a SQL Query that will not only select the fields from D, but join with table M, returning all the row values (only from one column) in a single result column, but all concatenated together (preferably with some sort of escaping so it can't be contaminated by rogue data in M)?

EDIT:

+-----------+----------+-------------+
| DataRowID | DataName | RandomField |
+-----------+----------+-------------+
| 1         | Foo      | Alice       |
+-----------+----------+-------------+
| 2         | Bar      | Bob         |
+-----------+----------+-------------+
| 3         | Baz      | Charlie     |
+-----------+----------+-------------+


+-----------+-----------+-----------+
| MetaRowID | DataRowID | MetaValue |
+-----------+-----------+-----------+
| 1         | 1         | Mercury   |
+-----------+-----------+-----------+
| 2         | 1         | Venus     |
+-----------+-----------+-----------+
| 3         | 1         | Earth     |
+-----------+-----------+-----------+
| 4         | 2         | Mars      |
+-----------+-----------+-----------+
| 5         | 2         | Jupiter   |
+-----------+-----------+-----------+
| 6         | 3         | Saturn    |
+-----------+-----------+-----------+
| 7         | 3         | Uranus    |
+-----------+-----------+-----------+
| 8         | 3         | Neptune   |
+-----------+-----------+-----------+
| 9         | 3         | Pluto     |
+-----------+-----------+-----------+

Which when Where DataName = 'Bar' is Queried,

+-----------+----------+-------------+--------------+
| DataRowID | DataName | RandomField | MetaData     |
+-----------+----------+-------------+--------------+
| 2         | Bar      | Bob         | Mars,Jupiter |
+-----------+----------+-------------+--------------+

Upvotes: 3

Views: 470

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

Use MySql's GROUP_CONCAT function:

SELECT d.ID, GROUP_CONCAT(m.Field) AS ConcatFields
FROM d JOIN m ON d.ID = m.dID
GROUP BY d.ID

Upvotes: 2

Related Questions