使用连接两次检索数据

Question

I have the following two MySQL queries.

$delivery_id =

    select c.countries_id 
           from orders o
           LEFT JOIN countries c
           ON c.countries_name = o.delivery_country
           where o.orders_id = 1208;

$billing_id =

    select c.countries_id 
           from orders o
           LEFT JOIN countries c
           ON c.countries_name = o.billing_country
           where o.orders_id = 1208;

With the results being put into the variables (PHP) as appropriate. What I would like to know is, is there a way to combine these two queries into one singular query instead of running it twice. (This is using ZenCart's Query Factory ($db->Execute) where the results will be put into a fields array and recalled as such.

Info about the tables: Each order in orders will contain a billing and delivery address with a country name being found in the appropriate field (billing_country and delivery_country). However, not all orders will contain a delivery address. How can I combine these two into one query? I know that I can use a LEFT JOIN of some sort but am not sure on how to write it.

Answer 1

You can join the countries table two times using different alias names:

select 
    c1.countries_id AS delivering_id,
    c2.countries_id AS billing_id
from orders o
LEFT JOIN countries c1
     ON c1.countries_name = o.delivery_country
LEFT JOIN countries c2
     ON c2.countries_name = o.billing_country
where o.orders_id = 1208;