How to double JOIN properly in SQL

Question

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 theaddress_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 1

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'