1

I am trying to get all matching results from a one to many relationship in 1 result.

Table structure

Users              owr_rosterdata               
--------           --------                 
id| name           id | user | info
-----------        ---------------------
1 | nealvdv        1  | 1 | aaaa
2 | another        2  | 1 | bbbb
                   3  | 2 | cccc
                   4  | 2 | dddd

My current query is something like this (without the WHERE clauses)

        "
        SELECT owr_rosterdata.*, users.id, users.name FROM owr_rosterdata 
        INNER JOIN users ON owr_rosterdata.user = users.id
        "

Current output

User 1: 1, nealvdv, aaaa

User 1: 1, nealvdv, bbbb

User 2: 1, another, cccc

User 2: 1, another, dddd

The whole point of this query is to avoid having multiple queries to get all matching results of a user into 1 result.

Desired output

User 1: 1, nealvdv, aaaa, bbbb

User 2: 2, another, cccc, dddd

Thank you for any suggestions!

NealVDV
  • 2,302
  • 3
  • 26
  • 51

1 Answers1

1

You can use GROUP_CONCAT for this:

SELECT users.id, users.name,
       GROUP_CONCAT(DISTINCT owr_rosterdata.info ORDER BY owr_rosterdata.info ASC ) 
FROM owr_rosterdata 
INNER JOIN users ON owr_rosterdata.user = users.id
GROUP BY users.id, users.name
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • I'm not sure who downvoted or why but I have read about the "GROUP_CONCAT". Is there no way to have it like it normally is rather then seperated by commas for example? – NealVDV Nov 11 '16 at 16:18