luweiqi luweiqi - 7 months ago 12
SQL Question

MySQL joining 3 tables using UNION

This question is a little long so that it would be clear, thanks in advance!

Introduction



I currently have 3 tables using a many-to-many relationship. I need to query all 3 tables and combine them into 1 table.




Problem



I have tried this query:

SELECT * FROM `login` LEFT JOIN membership ON login.id = membership.login_id UNION SELECT * FROM `login` RIGHT JOIN membership ON login.id = membership.login_id


And it returns:

+----+------+----------+
| id | name | group_id |
+----+------+----------+
| 1 | Tom | 6 |
| 2 | John | 8 |
| 3 | Jane | 4 |
+----+------+----------+





Question



I need it to also include the
group_name
. This is my desired output:

+----+------+----------+------------+
| id | name | group_id | group_name |
+----+------+----------+------------+
| 1 | Tom | 6 | Red |
| 2 | John | 8 | Brown |
| 3 | Jane | 4 | Purple |
+----+------+----------+------------+





Tables



login
Table


A list of all users with auto-increment id


+----+------+
| id | name |
+----+------+
| 1 | Tom |
| 2 | John |
| 3 | Jane |
+----+------+





group
Table


A list of all groups with the
group_id
and
group_name



+----------+------------+
| group_id | group_name |
+----------+------------+
| 1 | Green |
| 2 | Blue |
| 3 | Yellow |
| 4 | Purple |
| 5 | Orange |
| 6 | Red |
| 7 | Pink |
| 8 | Brown |
+----------+------------+





membership
Table


Stores information on which user belongs to which group


+----------+----------+
| login_id | group_id |
+----------+----------+
| 1 | 6 |
| 2 | 8 |
| 3 | 4 |
+----------+----------+

vkp vkp
Answer

Join the group table as well and select the required fields from the tables.

SELECT l.id,l.name,m.group_id,g.group_name
FROM  `login` l
LEFT JOIN `membership` m ON l.id = m.login_id 
LEFT JOIN `group` g on g.group_id = m.group_id
Comments