0

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;
2

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.