CODEWITHSUNDEEP
mysql
Ton Markus
Ton Markus

Reputation: 11

Get 2 results from 1 left join

I have a table with Orders. Each order has a Sender and a Receiver. Both the sender and the receiver are in the customers table. Each customer may be at some time a sender or a receiver, so I have no option to split them into a Sender and Receiver table.

I'm now stuck in constructing (in one query) a JOIN which will get me two customers (one as the Sender, one as the Receiver) that I can recognize as the "right" kind of customer.

I can make a standard LEFT JOIN, 

SELECT o.date,c.last_name,c.first_name,c.company,c.email
FROM orders o 
LEFT JOIN customers c ON ( o.sender = c.id )
WHERE o.id = 2";

but this will only get me the info on the sender. I can change o.sender to o.receiver, but that will only get me info for the receiver.

How can I get both and distinguish which of the two is the sender and which the receiver?

Thanks in advance,

Ton

Upvotes: 1

Views: 63

Answers (2)

Taryn
Taryn

Reputation: 247690

You can join on the customer table twice. Once will return the sender and the other will return the receiver.

SELECT o.date,
  c1.last_name,
  c1.first_name,
  c1.company,
  c1.email,
  c2.last_name,
  c2.first_name,
  c2.company,
  c2.email
FROM orders o 
LEFT JOIN customers c1
  ON o.sender = c1.id
LEFT JOIN customers c2
  ON o.receiver = c2.id
WHERE o.id = 2";

If you want to use more descriptive aliases, then you would use:

SELECT o.date,
  sender.last_name,
  sender.first_name,
  sender.company,
  sender.email,
  receiver.last_name,
  receiver.first_name,
  receiver.company,
  receiver.email
FROM orders o 
LEFT JOIN customers sender
  ON o.sender = sender.id
LEFT JOIN customers receiver
  ON o.receiver = receiver.id
WHERE o.id = 2";

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You could use an OR on the ON clause of the join:

SELECT o.date, c.last_name, c.first_name, c.company, c.email,
       case when o.sender=c.id then 'Sender'
            when o.receiver=c.id then 'Receiver' end as kind_of_customer
FROM orders o 
      LEFT JOIN customers c ON o.sender = c.id or o.receiver = c.id
WHERE o.id = 2;

and using a CASE WHEN you can distinguish if it's a sender or if it's a receiver.

Upvotes: 1

Related Questions