CODEWITHSUNDEEP
mysqljoinleft-join
Yeamin Chowdhury
Yeamin Chowdhury

Reputation: 512

mysql - join two tables to get data from same tables other row

I have two tables named users and requests structured as following:

users:

+----+----------+------------------------+
| id | name     | email                  |
+----+----------+------------------------+
| 1  | super    | [email protected]         |
+----+----------+------------------------+
| 2  | david    | [email protected]         |
+----+----------+------------------------+
| 3  | smith    | [email protected]         |
+----+----------+------------------------+
| 4  | philip   | [email protected]        |
+----+----------+------------------------+

requests:

+----+---------+----------------------+
| id | inviter | email                |
+----+---------+----------------------+
| 1  | 1       | [email protected]       |
+----+---------+----------------------+
| 2  | 2       | [email protected]       |
+----+---------+----------------------+
| 3  | 2       | [email protected]      |
+----+---------+----------------------+

Now I want to join this two table to get data as following:

+----+----------+------------------------+-----------+
| id | name     | email                  | inviter   |
+----+----------+------------------------+-----------+
| 1  | super    | [email protected]         | null      |
+----+----------+------------------------+-----------+
| 2  | david    | [email protected]         | super     |
+----+----------+------------------------+-----------+
| 3  | smith    | [email protected]         | david     |
+----+----------+------------------------+-----------+
| 4  | philip   | [email protected]        | david     |
+----+----------+------------------------+-----------+

Two tables will be joined on email attribute. And inviter field in resulting table is users name for corresponding id stored in requests table under inviter attribute.

I have wrote this query

select users.id as id, name, users.email as email, name, inviter
from users
left join requests on users.email = requests.email

which produce following result

+----+--------+-----------------+--------+---------+
| id | name   | email           | name   | inviter |
+----+--------+-----------------+--------+---------+
|  2 | david  | [email protected]  | david  |       1 |
|  3 | smith  | [email protected]  | smith  |       2 |
|  4 | philip | [email protected] | philip |       2 |
|  1 | super  | [email protected]  | super  |    NULL |
+----+--------+-----------------+--------+---------+

Can anyone help me to write a query to get data as intended?

Upvotes: 1

Views: 36

Answers (3)

Meet Soni
Meet Soni

Reputation: 130

You need to use left join twice with user table to get this, refer below db fiddle and query

SELECT US.*, US1.name 
FROM users AS US 
LEFT JOIN requests AS RQ ON RQ.email = US.email
LEFT JOIN users AS US1 ON US1.id = RQ.inviter

DB FIDDLE LINK: HERE

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

You need to join the table users for a second time to get the name of the inviter. For example:

select
  u.*,
  u2.name as inviter
from users u
left join requests r on r.email. = u.email
left join users u2 on u2.id = r.inviter
order by u.id

Upvotes: 0

forpas
forpas

Reputation: 164064

You must join users to requests and users again to get the name of the inviter:

select u.*, u2.name inviter
from users u 
left join requests r on r.email = u.email
left join users u2 on u2.id = r.inviter
order by u.id

See the demo.
Results:

| id  | name   | email           | inviter |
| --- | ------ | --------------- | ------- |
| 1   | super  | [email protected]  |         |
| 2   | david  | [email protected]  | super   |
| 3   | smith  | [email protected]  | david   |
| 4   | philip | [email protected] | david   |

Upvotes: 1

Related Questions