join three tables

Codes here !

Moderators: macek, egami, gesf

faust
New php-forum User
New php-forum User
Posts: 109
Joined: Thu May 03, 2012 7:22 am

join three tables

Postby faust » Tue Oct 30, 2012 4:42 am

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
New php-forum User
New php-forum User
Posts: 109
Joined: Thu May 03, 2012 7:22 am

Re: join three tables

Postby faust » Tue Oct 30, 2012 1:09 pm

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

MeroD
New php-forum User
New php-forum User
Posts: 53
Joined: Wed Oct 10, 2012 12:14 am

Re: join three tables

Postby MeroD » Wed Oct 31, 2012 11:39 pm

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
New php-forum User
New php-forum User
Posts: 109
Joined: Thu May 03, 2012 7:22 am

Re: join three tables

Postby faust » Sun Nov 04, 2012 2:53 pm

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
New php-forum User
New php-forum User
Posts: 53
Joined: Wed Oct 10, 2012 12:14 am

Re: join three tables

Postby MeroD » Mon Nov 05, 2012 1:53 am

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
New php-forum User
New php-forum User
Posts: 109
Joined: Thu May 03, 2012 7:22 am

Re: join three tables

Postby faust » Mon Nov 05, 2012 2:02 am

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 :)


Return to “mySQL & php coding”

Who is online

Users browsing this forum: Bing [Bot] and 1 guest