KBB KBB - 7 months ago 11
SQL Question

SQL join multiple columns into different rows

i have two tables name Combine and Product , combine has multiple products inside and combine has strictly 3 products so that i have two tables strutred as below:

Combine | id | name | image | item1 | item2 | item3
1 | exmpl | www.exmpl.com/exmp.jpg | 3 | 2 | 16

Product | id | name | image | stock
2 | productexmpl | www.product.com/product2.jpg | 3
3 | productexmpl2 | www.product.com/product3.jpg | 7
16 | productexmpl3 | www.product.com/product16.jpg | 3


What i want to get is search by combine id like
SELECT * FROM Combine Where id = ''
and get combine products in different rows , what i've tried is join tables with
SELECT * FROM Combine as c JOIN Product as p on c.item1 = p.id AND c.item2 = p.id AND c.item3 = p.id
but it joins information horizontally what i want is to get information vertically which means in different rows as below

id | name | image | stock
2 | productexmpl | www.product.com/product2.jpg | 3
3 | productexmpl2 | www.product.com/product3.jpg | 7
16 | productexmpl3 | www.product.com/product16.jpg | 3


i dont know if the structre is wrong by design but any help is appreciated

Thank you

Answer

you can try this query:

SELECT p.id, p.name, p.image, p.stock
FROM Combine as c
inner join
Product as p
on (c.item1=p.id or c.item2=p.id or c.item3=p.id)
Where id = ###
Comments