SQL join multiple columns into different rows

Question

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

I don't understand your example, but you could solve this by joining to the table 3 times, once on each statement. "JOIN product AS p1 ON p.id = c.item1" "JOIN product AS p2 ON p2.id = c.item2" — Arthur D
– Arthur D, Commented Apr 19, 2016 at 21:04

Iłya Bursov · Accepted Answer · 2016-04-19 21:10:22Z

1

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 = ###

answered Apr 19, 2016 at 21:10

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

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

1 Comment

Kaan Baris Over a year ago

Thank you , so basically or is doing this join separately for us

Collectives™ on Stack Overflow

SQL join multiple columns into different rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related