CODEWITHSUNDEEP
mysqlsql
Andrei L
Andrei L

Reputation: 3641

SQL. Count rows where 2 columns foreigns is same type

I have such a task, and I can't solve it.
I have these tables: 

users:
id | type

transactions:
id | sender_id | receiver_id

How can I make a select that will result this for each transactions row:

id | sender_id | receiver_id | sender_type | receiver_type

Tried JOIN but no success, I get only sender_id, receiver_id, type.
How can I get type column two times, for each one?
ex:

users:
id  | type
1   | 4
2   | 3
3   | 1
4   | 3

transactions:

id  | sender_id | receiver_id
1   | 2         | 3
2   | 1         | 4
3   | 3         | 1

result:
id  | sender_id | receiver_id | sender_type | receiver_type
1   | 2         | 3           | 3           | 1
2   | 1         | 4           | 4           | 3
3   | 3         | 1           | 1           | 4

Upvotes: 1

Views: 39

Answers (2)

Mark Williams
Mark Williams

Reputation: 2308

You need to join to the users table explicitly for each type (sender and receiver), so for example:

SELECT
    tr.id,
    tr.sender_id,
    tr.receiver_id,
    us_sn.type,
    us_rx.type
FROM
    transactions tr
JOIN 
    users us_rx ON tr.receiver_id = us_rx.id
JOIN 
    users us_sn ON tr.sender_id = us_sn.id

Upvotes: 1

O. Jones
O. Jones

Reputation: 108686

The trick you need for this is to JOIN the users table twice to the transactions table.

You'll need aliases to do this. I've used the one-letter aliases r and s. This is a very common application of the JOIN operation. It works as if there were two copies of the table.

It helps to create aliases for the column names too; some SQL clients work strangely when you feed them result sets with duplicate column names.

SELECT t.id, t.sender_id, t.receiver_id,
       s.type sender_type,
       r.type receiver_type
  FROM transactions t
  JOIN users s ON t.sender_id = s.id
  JOIN users r ON t.receiver_id = r.id
 ORDER BY t.id

Upvotes: 1

Related Questions