MySQL SELECT Query From Specific Columns

Question

I have the following MySQL table products:

id, code, name, color
1, 'D555', 'Men T-shirt', ''
2, 'D556', 'Black Men T-shirt', 'D556'
3, 'D557', 'Silver Men T-shirt', 'D556'
4, 'D558', 'Men T-shirt', ''
5, 'D559', 'Black Men T-shirt', 'D559'
6, 'D560', 'White Men T-shirt', 'D559'

The column color is not empty, if the product have another colors, for example: The product with code 'D556' is black and it has silver color, too.

I'm using SELECT * FROM products, but now I want to SELECT the main products (without the sub-colors), i.e: D555, D556, D558, D559.

And after that I'll SELECT the sub-colors under the product (since it's about e-store for clothes).

Is it possible with that kind of structure of the table?

Thank you very much for the attention!

Nadir Sampaoli · Accepted Answer · 2012-05-30 08:16:19Z

1

SELECT * FROM products WHERE color = '' or code = color;

You could consider normalizing your table in two tables like the following:

products(id, code, name)
alt_products(id, id_products, color)

answered May 30, 2012 at 8:16

Nadir Sampaoli

5,5654 gold badges24 silver badges34 bronze badges

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

1 Comment

user1410047 Over a year ago

Hihihi, very clever!! I'll use the first option, because I upload ~300 products every month and it's very hard to optimize for me. Thanks alot for the help!!!

Sergey · Accepted Answer · 2012-05-30 08:13:32Z

0

May be useful be this:

SELECT `id`, `code`, `name`, GROUP_CONCAT(`color` SEPARATOR ', ')  as `colors`
FROM products 
GROUP BY `code`

answered May 30, 2012 at 8:13

Sergey

5,20727 silver badges37 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT Query From Specific Columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related