CODEWITHSUNDEEP
mysqlsql
Zokrye
Zokrye

Reputation: 65

Joining on several columns

I have a database which allows to keep all exchanges of products between different stores. Here are the tables :

Delivery table:

+-------------+-----------+----------------+---------+
| delivery_id | origin_id | destination_id | ongoing |
+-------------+-----------+----------------+---------+
|          15 |         1 |              2 |       1 |
|          16 |         3 |              4 |       0 |
+-------------+-----------+----------------+---------+

Site table:

+---------+------------+----------------+
| site_id |       name |        address |
+---------+------------+----------------+
|       1 |     site_1 |            ... | 
|       2 |     site_2 |            ... | 
+-------------+--------+----------------+

The origin and destination ids are foreign keys referring to site_id.

My goal is to list all the ongoing deliveries with the names of origin and destination stores. For the example given above, the expected result would be:

+---------+------------+
| origin  |destination |
+---------+------------+
|  site_1 |     site_2 |
+---------+------------+

For the moment, the only way i have found to select the destination and origin ids with the associated delivery id and then joining these two results:

SELECT origin.origin, destination.destination FROM (SELECT site.name AS origin,delivery.delivery_id FROM delivery INNER JOIN site on site.site_id=delivery.origin_id WHERE delivery.ongoing=1) origin INNER JOIN (SELECT site.name as destination,delivery.delivery_id FROM delivery INNER JOIN site ON site.site_id=delivery.destination_id WHERE delivery.ongoing=1) destination ON origine.code=destination.code

I am pretty sure there is an easier and more efficient way to do that but I cannot find it. Can anybody confirm?

Upvotes: 2

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You seem to just want two joins:

select so.name as origin_name, sd.name as destination_name
from deliveries d left join
     sites so
     on d.origin_id = so.site_id left join
     sites sd
     on d.destination_id = sd.site_id
where d.ongoing = 1;

This uses left join just in case one of the columns in delivery has a NULL value. That actually seems unlikely in this case, so the outer join is probably not necessary.

Upvotes: 3

Related Questions