MySQL – How to Aggregate Results from SELECT JOIN

aggregatejoin;MySQL

I have two tables:

**user Table **

id   username
---  -----------
100  greg
101  john
102  patrick
103  bill

**user_subscription_plan Table **

id   user_id     amount
---  ---------   -------
1   100           50
2   100           52
3   101           10
4   103           100
5   103           110

I would like to make a query on the user table to extract username, but also to include id's from USER_SUBSCRIPTION which are related to the user_id

This is what I was trying:

SELECT
   u.id AS user_id,
   u.username AS username,
   usp.id AS subscription_id
   FROM user AS u
INNER JOIN user_subscription_plan usp on usp.id = u.id;

It gets me the results, but not in the form I need. If the user has two subscription entries, then it returns two rows for each subscription.

I would need to get only ids from Subscription table. Something like this:

SELECT
   u.id AS user_id,
   u.username AS username,
   usp.id AS subscription_ids -- Array of ids for each user
   FROM user AS u
INNER JOIN user_subscription_plan usp on usp.id = u.id;

Best Answer

Try with this

SELECT
   u.id AS user_id,
   u.username AS username,
   GROUP_CONCAT(usp.id) AS subscription_ids -- Array of ids for each user
   FROM user AS u
INNER JOIN user_subscription_plan usp on usp.id = u.id
GROUP BY u.id,u.username;

https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php#SP

Related Question