CODEWITHSUNDEEP
mysql
Anoop Sankar
Anoop Sankar

Reputation: 17

Need help to retrieve data from three MySQL tables

I have three tables named "users","user_hobbies" and "hobbies". Below is the sample tables with values;
Below is the users table with fields id, name and age

╔════╦══════╦═════╗
║ ID ║ NAME ║ AGE ║
╠════╬══════╬═════╣
║  1 ║ abc  ║  23 ║
║  2 ║ xyz  ║  24 ║
║  3 ║ pqr  ║  21 ║
╚════╩══════╩═════╝

and below is user_hobbies table with fields id, user_id and hobby_id

╔════╦═════════╦══════════╗
║ ID ║ USER_ID ║ HOBBY_ID ║
╠════╬═════════╬══════════╣
║  1 ║       1 ║        1 ║
║  2 ║       1 ║        2 ║
║  3 ║       1 ║        3 ║
║  4 ║       2 ║        4 ║
║  5 ║       2 ║        3 ║
║  6 ║       2 ║        5 ║
║  7 ║       3 ║        2 ║
║  8 ║       4 ║        6 ║
╚════╩═════════╩══════════╝

. Below is the hobbies table with fields id and desc

╔════╦═══════════╗
║ ID ║   DESC    ║
╠════╬═══════════╣
║  1 ║ music     ║
║  2 ║ chatting  ║
║  3 ║ cricket   ║
║  4 ║ badminton ║
║  5 ║ chess     ║
║  6 ║ cooking   ║
╚════╩═══════════╝

. The actual requirement is that i need a query to retrieve name, age, hobby_id and desc (see an example below)

╔══════╦═════╦══════════╦═════════════════════════╗
║ NAME ║ AGE ║ HOBBYID  ║        DESC             ║
╠══════╬═════╬══════════╬═════════════════════════╣
║ abc  ║  23 ║ 1,2,3    ║ music,chatting,cricket  ║
║ pqr  ║  21 ║ 2        ║ chatting                ║
║ xyz  ║  24 ║ 4,3,5    ║ badminton,cricket,chess ║
╚══════╩═════╩══════════╩═════════════════════════╝

Upvotes: 1

Views: 51

Answers (1)

John Woo
John Woo

Reputation: 263723

You need to join the tables first and use an aggregate function called GROUP_CONCAT().

SELECT  a.Name,
        a.Age,
        GROUP_CONCAT(c.ID) hobbyIDs,
        GROUP_CONCAT(c.desc) descList
FROM    users a
        INNER JOIN user_hobbies b
            ON a.ID = b.user_ID
        INNER JOIN hobbies c
            ON b.hobby_ID = c.ID
GROUP   BY a.Name, a.Age

To further gain more knowledge about joins, kindly visit the link below:

OUTPUT

╔══════╦═════╦══════════╦═════════════════════════╗
║ NAME ║ AGE ║ HOBBYIDS ║        DESCLIST         ║
╠══════╬═════╬══════════╬═════════════════════════╣
║ abc  ║  23 ║ 1,2,3    ║ music,chatting,cricket  ║
║ pqr  ║  21 ║ 2        ║ chatting                ║
║ xyz  ║  24 ║ 4,3,5    ║ badminton,cricket,chess ║
╚══════╩═════╩══════════╩═════════════════════════╝

Upvotes: 1

Related Questions