I've 4 tables, Transactions, Orders - SuperType, after which Site_Orders and Product_Orders SubType
[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 ==
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?
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'