Mysql group query

Question

I have two tables containing product information, and product variations. I’m trying to list all products, and it’s colours variations.

The table products contains general information’s about the product (name, description, enabled etc.)

The table product_attributes contains the different options of the product (color, size, price etc.)

I’m trying to get all available products, and if the product is available in two colors, it should show as two listings. Unfortunately my query below list far more options, since there will be a row for each size combination as well.

SELECT pa.price, pa.color, p.id, p.url_key, p.name FROM product_attributes AS pa INNER JOIN products AS p ON pa.product_id = p.id && p.active = 1

Does anyone have an idea how to achieve this, the best and smartest way?

I hope you understand my question

Thanks in advance,

Regards!

id  mediumint(7)    NO  PRI NULL    auto_increment
type    tinyint(3)  NO      1    
url_key varchar(54) NO      NULL     
name    varchar(48) NO      NULL     
description text    NO      NULL     
weight  float   NO      0    
active  tinyint(3)  NO      0    
in_feed tinyint(3)  NO      0    
tax_class   tinyint(3)  NO      0    
meta_title  varchar(48) NO      NULL     
meta_keywords   varchar(48) NO      NULL     
meta_description    text    NO      NULL     
manage_stock    tinyint(3)  NO      0    
attribute_designers_id  mediumint(7)    NO      0    
attribute_size_family_id    mediumint(7)    NO      0

id  mediumint(7)    NO  PRI NULL    auto_increment
product_id  mediumint(7)    NO  MUL NULL     
sku varchar(48) NO      NULL     
price   float   NO      NULL     
special_price   float   NO      0    
special_price_from_date datetime    NO      NULL     
special_price_to_date   datetime    NO      NULL     
cost    float   NO      0    
new_from_date   datetime    NO      NULL     
new_to_date datetime    NO      NULL     
attribute_colors_id mediumint(7)    NO      NULL     
color   varchar(24) NO      NULL     
attribute_sizes_id  mediumint(7)    NO      NULL     
quantity    mediumint(7)    NO      0

Answer 1

You need to add a group by:

SELECT pa.price, pa.color, p.id, p.url_key, p.name
FROM product_attributes AS pa
INNER JOIN products AS p
ON pa.product_id = p.id && p.active = 1
GROUP BY p.id, pa.color

Note that your column pa.price probably makes no sense here as the price almost certainly depends on the size, but you don't know which size the price is for.