CODEWITHSUNDEEP
databasemysql
William
William

Reputation: 1033

How to pick table in query based on subtype?

I have 4 tables, Transactions, Orders - SuperType, and then 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 question is, how do i create a join statement that will pull transactions and associate and grab the right information from the subType table? Or will i have to use PHP to check the subtype and have the logic done there?

Thanks

Upvotes: 1

Views: 487

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

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'

Upvotes: 2

Related Questions