SQL如果连接不匹配则连接其他内容

Question

措辞不好，因为我对 SQL 中的这些操作不熟悉，假设我有一个包含东部和西部地区的表，另一个表包含一个名为 address_state 的字段。我希望能够加入等于 state_abbreviation 或 state_name 的 UCASE

EG:

SELECT orders.total_value, territories.territory FROM orders 
INNER JOIN tblCanadianTerritories ON 
UCASE(orders.technical_address_state) = tblCanadianTerritories.state 
OR 
tblCanadianTerritories ON 
UCASE(orders.technical_address_state) = tblCanadianTerritories.name

WHERE UCASE(orders.technical_address_country) = "CANADA"

编辑:

得出了两种可能的解决方案:

SELECT so_order.id, so_order.date_entered, so_order.check_if_new_customer, tblCanadianTerritories.territory, so_order.total_value  FROM so_order
INNER JOIN tblCanadianTerritories ON 
UCASE(so_order.technical_address_state) = tblCanadianTerritories.state   
OR
UCASE(so_order.technical_address_state) = tblCanadianTerritories.name
WHERE UCASE(so_order.technical_address_country) = "CANADA"
ORDER BY so_order.date_entered ASC

或者

SELECT so_order.id, so_order.date_entered, so_order.check_if_new_customer, 

tblCanadianTerritories.territory, so_order.total_value  FROM so_order
INNER JOIN tblCanadianTerritories ON UCASE(so_order.technical_address_state) = 

tblCanadianTerritories.state   
WHERE UCASE(so_order.technical_address_country) = "CANADA"
ORDER BY so_order.date_entered ASC
UNION 
SELECT so_order.id, so_order.date_entered,so_order.check_if_new_customer, 

tblCanadianTerritories.territory, so_order.total_value FROM so_order
INNER JOIN tblCanadianTerritories ON UCASE(so_order.technical_address_state) = 

tblCanadianTerritories.name
WHERE UCASE(so_order.technical_address_country) = "CANADA"
ORDER BY so_order.date_entered;

但它们似乎都检索到不同的结果。

Answer 1

试试这个:

SELECT orders.total_value, territories.territory 
    FROM orders AS so_order INNER JOIN tblCanadianTerritories 
        ON (
            UCASE(so_order.technical_address_state) = tblCanadianTerritories.state 
            OR 
            UCASE(so_order.technical_address_state) = tblCanadianTerritories.name
             )
WHERE UCASE(so_order.technical_address_country) = "CANADA"

如果您想使用 UNION，请尝试以下操作:

SELECT orders.total_value, territories.territory 
    FROM orders AS so_order  INNER JOIN tblCanadianTerritories 
        ON UCASE(so_order.technical_address_state) = tblCanadianTerritories.state 
WHERE UCASE(so_order.technical_address_country) = "CANADA"
UNION
SELECT orders.total_value, territories.territory 
    FROM orders AS so_order  INNER JOIN tblCanadianTerritories 
        ON UCASE(so_order.technical_address_state) = tblCanadianTerritories.name 
WHERE UCASE(so_order.technical_address_country) = "CANADA"