SQL JOIN to get two records for each row

Question

I have two tables, say

TABLE: Transactions

COLUMNS: sender_id, receiver_id, value

and

TABLE: Users

COLUMNS: user_id, username

Is it possible to make a SQL statement to JOIN two rows from the users table for each record in the transactions table? So for each result row, I should have two usernames from the users table, but with two different aliases. How to achieve this?

Thanks

At the very least provide sample data and the desired output. — PM 77-1, Jun 27 '15 at 23:58
Yes, it is possible. However you have to show research yourself. So get a bit better information and an example in here. — Norbert, Jun 27 '15 at 23:59
possible duplicate of [Full Outer Join in MySQL](http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql) — Norbert, Jun 28 '15 at 00:01
didn't add sample data because I thought the table/field names would be intuitive enough on what i wanted to achieve. — zantuja, Jun 28 '15 at 00:34

score 0 · Answer 1 · answered Jun 28 '15 at 00:01

You have the right idea, by talking about aliases. You need two joins:

select us.username, ur.username, t.value
from transactions t left join
     users us
     on t.sender_id = us.user_id left join
     users ur
     on t.receiver_id = ur.receiver_id;

left join is just in case one of the values does not match.

score 0 · Accepted Answer · answered Jun 28 '15 at 00:01

Assuming sender_id and receiver_id are references to a user_id, and assuming that user_id is UNIQUE in the Users table... yes.

Join to the Users table twice.

For example:

  SELECT t.sender_id
       , t.receiver_id
       , t.value
       , s.user_id   AS sender_user_id
       , s.username  AS sender_username
       , r.user_id   AS receiver_user_id
       , r.username  AS receiver_username
    FROM `Transactions` t
    LEFT
    JOIN `Users` s ON s.user_id = t.sender_id 
    LEFT
    JOIN `Users` r ON r.user_id = t.receiver_id
   ORDER BY 1,2,3

The query is using outer joins... the row from Transactions will be returned if receiver_id or sender_id doesn't match a row in the Users table.

The keyword LEFT can be removed to change that behavior, so a row will be returned only if there is a matching row in Users for both sender_id and receiver_id.

SQL JOIN to get two records for each row

2 Answers2