mysql - 如何在 SQL 中正确地加倍 JOIN

Question

我正在尝试选择 billing_address 城市在“纽约”但 shipping_address 城市不在“纽约”的所有交易。我正在努力解决的问题是，在查看 transactions 表时，有两列(shipping_address_id 和 billing_address_id)，其中各自的 id两者都作为两个单独的记录存储在 address_table 中。

因为我需要检查 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;

Answer 1

假设我理解正确，您只需要使用内部联接:

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'