How to get mysql result in following format

Question

Database Tables: The below tables contain the information about users and their licenses.

Note: One user can have multiple licenses. users table Users( 'user_id', 'user_email', 'name' )

license table Licenses( 'license_id', 'user_id', 'license_key', 'license_status' )

Write an optimized MySql query to generate the output like

Desired output

Edit:

I have tried this SELECT u.user_email, u.name as user_name, l.license_status as status, l.license_key FROM users u left join licenses l on u.user_id = l.user_id but it's not showing expected result and I'm not sure how can I get the Desired output

Answer 1

GROUP_CONCAT to the rescue:

SELECT  u.user_email,
        u.name as user_name,
        GROUP_CONCAT(l.license_status ORDER BY license_id) AS statuses,
        GROUP_CONCAT(l.license_key ORDER BY license_id) AS "keys"
    FROM  users u
    left join  licenses l  ON u.user_id = l.user_id
    GROUP BY u.user_id

If desired change to

   COALESCE(GROUP_CONCAT(...), "none") AS ...

If you don't want the users with no licenses, change LEFT JOIN to JOIN.