MySQL 如何在 SQL 中正确地加倍 JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40066386/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to double JOIN properly in SQL
提问by Robby Williams
I'm trying to select all transactionswith 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 transactionstable, there's two columns (shipping_address_idand billing_address_id), where the respective id for both is stored in theaddress_tableas two seperate records.
我正在尝试选择所有transactions带有“纽约”的 billing_address 城市，但带有“shipping_address”的城市不在“纽约”中。我正在努力解决的问题是，在查看transactions表时，有两列（shipping_address_id和billing_address_id），其中两列各自的 id 存储在address_table两个单独的记录中。
Since I need to check whether or not the shipping/billing address is 'New York' for both those columns in the transactionstable, I'm trying to do a double join to the address_tablein my query, though it doesn't seem to be working properly. Does anyone see where I'm going wrong here? Thanks!
由于我需要检查表中这两列的送货/帐单地址是否为“纽约” transactions，因此我正在尝试对address_table查询中的进行双重连接，尽管它似乎不起作用适当地。有没有人看到我哪里出错了？谢谢！
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;
回答by sgeddes
Assuming I'm understanding correctly, you just need to use an inner join:
假设我理解正确，你只需要使用一个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'
