如何在基于子类型的查询中选择表？

Question

我有4个表，交易，订单 - 超，然后Site_Orders和Product_Orders子类型

[Transactions] 
id PK 
Orders_id [FK Orders.orders_id] 

[Orders] 
Orders_id PK 
Orders_type 

[Site_Orders] 
Orders_id [FK Orders.orders_id] 
== other data == 

[Product_Orders] 
Orders_id [FK Orders.orders_id] 
== other data == 

我的问题是，如何创建一个连接语句，将拉动交易和关联，并抢在正确的信息子类型表？或者我将不得不使用PHP来检查子类型并在那里完成逻辑？

感谢

Answer 1

SELECT ... 
FROM Orders o 
JOIN Transactions t ON t.Orders_id = o.Orders_id 
LEFT OUTER JOIN Site_Orders so 
    ON so.Orders_id = o.Orders_id AND o.Orders_type = 'S' 
LEFT OUTER JOIN Product_Orders po 
    ON po.Orders_id = o.Orders_id AND o.Orders_type = 'P'