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.
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;