CODEWITHSUNDEEP
mysqlsql
laurent
laurent

Reputation: 515

SQL JOIN query syntax

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.

Upvotes: 0

Views: 260

Answers (6)

Jonathan Chow
Jonathan Chow

Reputation: 703

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

Upvotes: 0

dratewka
dratewka

Reputation: 2114

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.

Upvotes: 0

Roland Bouman
Roland Bouman

Reputation: 31961

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

Upvotes: 0

user1102001
user1102001

Reputation: 707

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....

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

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
.
.

Upvotes: 1

Matteo Tassinari
Matteo Tassinari

Reputation: 18584

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.

Upvotes: 2

Related Questions