Select order products in MySQL from muliple tables

Question

I have 3 tables:

• orderProducts (orderId, productId, productValue),
• products (productId, productName)
• values (productId, productValue, productValuePrice).

I need to select order products (product id, name, value, price) with defined orderId. How to do that with one MySQL query?

Answer 1

You can do this via a join, something like:

Select o.orderId, p.productId, p.name, o.productValue, v.productValuePrice
From products p
     Join values v on p.productId = v.productId
     Join orderProducts o on p.productId = o.orderId
Where orderId = 5

Answer 2

.......

 select p.productId, p.productName, o.productValue, v.prodctValuePrice from orderProducts o  join products p on o.productId = p.productId join values v on v.productId = p.productId where p.productId = 1

Answer 3

You could use a left join to return rows in the orderProducts table for which no corresponding rows in the other tables exist. Any missing columns will be NULL, allowing you to flag an error condition. The default join is an inner join, and will only return rows with matching entries in both joined tables.

select op.product id, p.name, v.productValue, p.productValuePrice
from orderProducts op
left join products p on p.productId = op.productId
left join values v 
    on v.productId = op.productId
    and v.productValue = op.productValue
where op.orderId = <YourOrderId>