I've 4 tables, Transactions, Orders - SuperType, after which Site_Orders and Product_Orders SubType

id PK
Orders_id [FK Orders.orders_id]

Orders_id PK

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

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

My real question is, how do you produce a join statement which will pull transactions and connect and grab the best information in the subType table? Or will i must use PHP to determine the subtype and also have the logic done there?


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'