mysql JOIN between three tables

Question

I have 4 tables in my database:

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

Relationship between users to roles is one to one
Relationship between users positions is many to many

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 ?

Show us sample data and desire output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jun 3, 2016 at 14:22

mcastilloy2k · Accepted Answer · 2016-06-03 14:21:49Z

1

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

answered Jun 3, 2016 at 14:21

mcastilloy2k

4657 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql JOIN between three tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related