CODEWITHSUNDEEP
mysqljoin
KMatko
KMatko

Reputation: 161

Mysql - merge two tables

I've got data in two tables:

table products

ID | title
1  | T-shirt
2  | Pants
...

and table details

productID | key | value
1         | color | green
1         | size  | M
1         | size  | L
2         | color | white
2         | color | black
2         | brand | n/a
...

So, each product in table has a lot of details. I would like to write SQL which give me result:

ID | title   | color       | size | brand
1  | T-shirt | green       | M,L  |
2  | Pants   | white,black |      | n/a

Right now my first SQL is:

SELECT * FROM products;

and then in while loop each time I call:

SELECT * FROM details WHERE productID={id}

and then merge data together. Is there maybe an easy way? Thanks!

Edit: Data was imported to mysql and I don't know all details per product (if I would know than I would put some extra columns to products table). And details are changing each day.

Upvotes: 0

Views: 62

Answers (2)

univise
univise

Reputation: 509

As I understand the question you would like a result table with the data from both tables:

select products.ID, products.title, details.* 
from products
inner join details
on details.productID = products.ID;

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I would do this using conditional aggregation and group_concat():

select p.id, p.title,
       group_concat(case when key = 'color' then value end) as colors,
       group_concat(case when key = 'size' then value end) as sizes,
       group_concat(case when key = 'brand' then value end) as brands
from products p join
     details d
     on p.id = d.productid
group by p.id, p.title;

Upvotes: 2

Related Questions