Robby Williams Robby Williams - 1 month ago 6
MySQL Question

How to double JOIN properly in SQL

I'm trying to select all

transactions
with the billing_address city in 'New York', but with the shipping_address city not in 'New York'. The problem I'm struggling with is that when looking at the
transactions
table, there's two columns (
shipping_address_id
and
billing_address_id
), where the respective id for both is stored in the
address_table
as two seperate records.

Since I need to check whether or not the shipping/billing address is 'New York' for both those columns in the
transactions
table, I'm trying to do a double join to the
address_table
in my query, though it doesn't seem to be working properly. Does anyone see where I'm going wrong here? Thanks!

SELECT billing.id AS billing_address, shipping.id AS shipping_address
FROM transactions AS t
LEFT JOIN address_table AS billing
ON t.billing_address_id = billing.id
AND billing.city = 'New York'
AND t.billing_address_id IS NOT NULL
LEFT JOIN address_table AS shipping
ON t.shipping_address_id = shipping.id
AND shipping.city != 'New York'
AND t.shipping_address_id IS NOT NULL;

Answer

Assuming I'm understanding correctly, you just need to use an inner join:

SELECT t.*,
       b.id AS billing_address, 
       s.id AS shipping_address
FROM transactions AS t
       JOIN address_table AS b ON t.billing_address_id = b.id      
       JOIN address_table AS s ON t.shipping_address_id = s.id
WHERE b.city = 'New York' AND
      s.city != 'New York'
Comments