SQL JOIN query syntax

Question

I have a table where I store purchases, and a table where I store products.

Each row in the purchases table can contain up to 9 products ids (8 can be empty)

id, foo, bar, baz, product_1_id, product_2_id, product_3_id, etc...

Now I need to query all the fields of that purchases table, to display in a html page.

I can't display only the products ids, so I need to find the corresponding product_name in the products table.

The SQL query is giving me a hard time.

Any help would be really appreciated.

Shouldn't you just have a products table, instead of limiting a purchase to nine products? — Waleed Khan
– Waleed Khan, Commented Jul 31, 2012 at 13:51
"Each row in the purchases table can contain up to 9 products ids (8 can be empty)". Typically this would be stored in a separate table, "purchase_item" or "purchase_product". I guess "purchase" is not really a good name since that refers to one individual item. Classic names are "purchase_order" and "purchase_order_item" or "purchase_order_line". The problem with your design is that it's not flexible (only up to 9 products per purchase) and that it is hard to query. You'll need a join for each potential product per purchase. With a separate purchase_order_item table you need just one. — Roland Bouman
– Roland Bouman, Commented Jul 31, 2012 at 13:53
I wonder what message you'll send to the user when he/she adds a 10th product to the order. "Sorry but we want to sell you only 9 items?" :) — Bazzz
– Bazzz, Commented Jul 31, 2012 at 14:01
Your comments are right, I must admit. BUT, if someone ever wants to buy ten products at once, I'd be happy to rethink everything from scratch, free of charge... ;) — laurent
– laurent, Commented Jul 31, 2012 at 14:22

Matteo Tassinari · Accepted Answer · 2012-07-31 13:53:14Z

2

To retrieve the product names, I'd try this:

SELECT t2.product_name as product_name_1, t3.product_name as product_name_2, t4.product_name as product_name_1, [...]
FROM purchases as t1
JOIN products as t2 ON(t1.product_1_id = t2.product_id)
LEFT JOIN products as t3 ON(t1.product_2_id = t3.product_id)
LEFT JOIN products as t4 ON(t1.product_3_id = t4.product_id)
[...]
WHERE t1.id = ...

but this will probably be VERY slow, you might want to restructure your database.

answered Jul 31, 2012 at 13:53

Matteo Tassinari

18.6k8 gold badges65 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Madhivanan · Accepted Answer · 2012-07-31 13:53:26Z

1

You need to join with Products table 9 times. But consider normalisation

select id, foo, bar, baz, p1.product_name, p2.product_name, p3.product_name, 
  etc...  
from purchases as pur
left join products as p1 on pur.product_1_id=p1.product_id
left join products as p2 on pur.product_2_id=p2.product_id
.
.

answered Jul 31, 2012 at 13:53

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Comments

user1102001 · Accepted Answer · 2012-07-31 13:54:47Z

0

in sql you can use like this..

 select *,PM.Product_id,P.Product_name from purchase_mst PM, product P where   
 PM.Product_id1=P.Product_id1 and PM.Product_id2=P.Product_id2 etc....

answered Jul 31, 2012 at 13:54

user1102001

7072 gold badges10 silver badges21 bronze badges

Comments

Roland Bouman · Accepted Answer · 2012-07-31 13:57:08Z

0

With a properly normalized design you'd have: product, purchase_Order and purchase_order_line and the query would simply be:

SELECT     ...
FROM       purchase_order
INNER JOIN purchase_order_line
ON         purchase_order.id = purchase_order_line.purchase_order_id
INNER JOIN product
ON         purchase_order_line.product_id = product.id

answered Jul 31, 2012 at 13:57

Roland Bouman

32.1k8 gold badges69 silver badges69 bronze badges

3 Comments

laurent Over a year ago

Thank you Roland. I'd love to learn. Where can I find informations about properly normalized designs ?

Roland Bouman Over a year ago

there is a lot of stuff on the web, just google it. For example, this is a reasonable start databases.about.com/od/specificproducts/a/normalization.htm and so is this dreamincode.net/forums/topic/…

laurent Over a year ago

dreamincode.net/forums/topic/… is really helpful. Thanks Roland.

dratewka · Accepted Answer · 2012-07-31 14:02:22Z

0

I think it would be easier if you had a table connecting purchases and products with two columns *product_id, purchase_id*.

But still, I guess you can try something like this :

SELECT pr1.name
 FROM purchases pur1, products pr1
WHERE pur1.product_1_id = pr1.id
UNION ALL
SELECT pr2.name
 FROM purchases pur2, products pr2
WHERE pur2.product_1_id = pr2.id
UNION ALL
-- and so on until
SELECT pr9.name
 FROM purchases pur9, products pr9
WHERE pur9.product_9_id = pr9.id

Although this looks pretty scary and I would strongly suggest rethinking your database design.

answered Jul 31, 2012 at 14:02

dratewka

2,11414 silver badges15 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:29:12Z

0

You could do this

Select purchase., product. from purchase join product on id1=id, id2=id... Etc.

Read this:

MySQL how to join tables on two fields

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Jul 31, 2012 at 14:03

Jonathan Chow

7037 silver badges29 bronze badges

Collectives™ on Stack Overflow

SQL JOIN query syntax

6 Answers 6

Comments

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related