Select order products in MySQL from muliple tables

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?

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>

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

.......

 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