join three tables

[faust]

I've never been good with complex queries (not that this is a complex one). I have three tables:
Order
Order_product
Product

Each product has manufacturer_id. There can be more than one product in one order (that's why there is a cross table 'Order_product'). The products in a single order can have different manufacturer ids.
I need to select all orders where there is at least one product from the currently logged manufacturer (I have his id).
Hope I have explained my problem clear enough. If you need more info ask me. Cheers !

[faust]

so, no one knows how I can join three tables?

[MeroD]

The products in a single order can have different manufacturer ids. -> This sounds really confusing. Why is there not a Product_Manufacturer linking table then?

I'm not even going to warrant a guess. Maybe posting up the structure of those three tables would help?

[faust]

table orders
id
product_id

table products
id
manufacturer_id
catalog_code

table order_products
id
order_id
product_id

table manufacturers
id
name
email
address

This is the base structure. I have one order with many products. Each product can have only one manufacturer but in the order, there may be products with different manufacturers. Hope I explained my question clearer this time. Thanks for your time. Cheers

[MeroD]

Your orders table should not contain product_id since one order may have multiple products (which you already have in order_products anyway.) The order table usually carries order_date and client_id for example...

If you are only dealing with a single manufacturer_id in your query, why are you concerned that orders may contain products from different manufacturers?

Anyway, here are two joins you can try:
SELECT p.manufacturer_id, o.id AS order_id, r.product_id
FROM orders o, order_products r, products p
WHERE o.id = r.order_id
AND r.product_id = p.id
AND p.manufacturer_id = x
ORDER BY o.id

SELECT p.manufacturer_id, o.id AS order_id, r.product_id
FROM orders AS o
INNER JOIN order_products AS r
ON o.id = r.order_id
INNER JOIN products AS p
ON r.product_id = p.id
WHERE p.manufacturer_id = x
ORDER BY o.id

* Just use DISTINCT o.id if you only want the order id's...

[faust]

I made a mistake. I don't have product_id in the orders table, sorry. Good you noticed that. I'll try your suggestion later and share the results. Thanks for the help