Robby Williams Robby Williams - 1 year ago 65
MySQL Question

How to double JOIN properly in SQL

I'm trying to select all

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
table, there's two columns (
), where the respective id for both is stored in the
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
table, I'm trying to do a double join to the
in my query, though it doesn't seem to be working properly. Does anyone see where I'm going wrong here? Thanks!

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

Answer Source

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

SELECT t.*, AS billing_address, AS shipping_address
FROM transactions AS t
       JOIN address_table AS b ON t.billing_address_id =      
       JOIN address_table AS s ON t.shipping_address_id =
WHERE = 'New York' AND != 'New York'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download