CODEWITHSUNDEEP
mysqljoinnested
deshi
deshi

Reputation: 37

mysql JOIN between three tables

I have 4 tables in my database: 

users (id,name)
roles (id,name)
positions (id,name)
position_user (user_id,position_id)

i want to take all users with their role name and list with their positions but i don't know how to structure my query. I think that one of my query must be something like this:

SELECT pu.user_id AS user_id, 
       group_concat(p.name separator ',') AS list_pos
FROM position_user pu 
INNER JOIN positions p 
        ON p.id = pu.position_id 
GROUP BY pu.user_id

And other one must be like this : 

SELECT users.id, users.first_name, roles.name
FROM users
JOIN roles 
  ON users.role_id = roles.id

Can I combine these two in one query and how ?

Upvotes: 1

Views: 54

Answers (1)

mcastilloy2k
mcastilloy2k

Reputation: 466

Try something like this and check the MySQL documentation.

SELECT pu.user_id AS user_id, u.first_name, r.name as rol_name, group_concat(p.name separator ',') AS list_pos
    FROM position_user pu 
    INNER JOIN positions p ON p.id = pu.position_id 
    INNER JOIN users u ON u.id = pu.uder_id
    INNER JOIN roles R ON u.role_id = r.id
    GROUP BY pu.user_id, u.first_name, r.name

Upvotes: 1

Related Questions