CODEWITHSUNDEEP
mysql
JREAM
JREAM

Reputation: 5931

MySQL Join Table with String of other Records

Can you help me cause all the records in the "preference" table to become a string inside the p.data returned result?

I could only think of CONCAT, but my inner join only selects 1 record.

Example Query

SELECT 
    u.userid, 
    p.data

FROM user u

INNER JOIN preference p ON (
  p.userid = u.userid
)

WHERE u.userid = 1

Desired Output

userid  |  data
--------------
1       | 1,2,3,4,5
2       | 1,2,3,4
3       | 1,2,3

Upvotes: 2

Views: 455

Answers (2)

Gonzalo.-
Gonzalo.-

Reputation: 12672

Doesn't matter your Inner join only join one field. You have to add group by

SELECT 
    u.userid, 
    GroupConcat(p.data) as Data

FROM user u

INNER JOIN preference p ON (
  p.userid = u.userid
)
--WHERE u.userid = 1 With this you not gonna get the desired output.
group by u.userId

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270637

MySQL's GROUP_CONCAT() performs exactly this function:

SELECT
  u.userid,
  GROUP_CONCAT(p.data) AS data
FROM
  user u
  INNER JOIN preference p ON u.userid = p.userid
WHERE u.userid = 1
GROUP BY u.userid

You only get one row back because of your WHERE clause. Remove the WHERE clause to return rows for all users. And switch it to a LEFT JOIN if you want to return users having no related rows in the preference table.

Upvotes: 3

Related Questions