sql - 使用来自多个表的值填充单个列

Question

我有三个表如下：

订单表

| OrderID  | Type      | ItemID |
+----------+-----------+---------+
| 0001     | Car       | A001    |
| 0002     | Motorbike | A001    |

Car_Table

| ItemID  | ItemDesc   |
+---------+------------+
| A001    | Honda Jazz |

Motorbike_Table

| ItemID  | ItemDesc   |
+---------+------------+
| A001    | Yamaha F1Z |

我需要通过添加一个名为的列将最后两个表合并到第一个表中ItemDesc。

有什么方法可以进行查询以根据ItemDesc列的值Car_Table或Motorbike_Table根据列的值填充Type列？回答：

SELECT Order_Table.OrderID, Order_Table.Type, Order_Table.ItemID,
IIF(Order_Tbl.Type = "Car", Car_Tbl.ItemDesc, Motorbike_Tbl.ItemDesc) AS ItemDesc
FROM Order_Table 
LEFT JOIN Car_Table ON Order_Table.ItemID = Car_Table.ItemID
LEFT JOIN Motorbike_Table ON Order_Table.ItemID = Motorbike_Table.ItemID;

谢谢！

标签： sqlms-access

Answer 1

您可以使用条件聚合来获得所需的结果，从Car_tablewhenType = Car和 from Motorbike_tablewhen中选择一个值Type = Motorbike：

SELECT Order_Table.OrderID, Order_Table.Type, Order_Table.ItemID,
IIF(Order_Tbl.Type = "Car", Car_Tbl.ItemDesc, Motorbike_Tbl.ItemDesc) AS ItemDesc
FROM Order_Table 
LEFT JOIN Car_Table ON Order_Table.ItemID = Car_Table.ItemID
LEFT JOIN Motorbike_Table ON Order_Table.ItemID = Motorbike_Table.ItemID;

输出：

OrderID     Type        ItemID  ItemDesc
1           Car         A001    Honda Jazz
2           Motorbike   A001    Yamaha F1Z

dbfiddle 上的演示