André Dias André Dias - 4 months ago 9
SQL Question

Select a specific record in a group of records for all groups of records in MySQL

I have a table that contains many different records for products specifications, the products may appear more than once in the table as they have different colors. In order to display the products in a screen I need to select a list of them with the color YELLOW, but if YELLOW is not present I need color BLUE, or else I don't want this product.

Simplified Products example:


+----+--------+
| ID | NAME |
+----+--------+
| 1 | Prod A |
| 2 | Prod B |
| 3 | Prod C |
| 4 | Prod D |
+----+--------+


Simplied Spec table:


+----+------------+--------+
| ID | ID_PRODUCT | COLOR |
+----+------------+--------+
| 1 | 1 | BLUE |
| 2 | 1 | YELLOW |
| 3 | 2 | RED |
| 4 | 2 | PINK |
| 5 | 3 | BLUE |
| 6 | 3 | GRAY |
| 7 | 4 | YELLOW |
+----+------------+--------+


Expected results:


+----+------------+--------+
| ID | ID_PRODUCT | COLOR |
+----+------------+--------+
| 2 | 1 | YELLOW |
| 5 | 3 | BLUE |
| 7 | 4 | YELLOW |
+----+------------+--------+


Raw SQL for this example:


CREATE TABLE `colors` (
`ID` int(11) NOT NULL,
`ID_PRODUCT` int(11) DEFAULT NULL,
`COLOR` varchar(16) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `products` (
`ID` int(11) NOT NULL,
`NAME` varchar(16) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `colors` VALUES (1,1,'BLUE'),(2,1,'YELLOW'),(3,2,'RED'),(4,2,'PINK'),(5,3,'BLUE'),(6,3,'GRAY'),(7,4,'YELLOW');

INSERT INTO `products` VALUES (1,'Prod A'),(2,'Prod B'),(3,'Prod C'),(4,'Prod D');

Answer

If it's always going to be blue and yellow comparison then I'd just use max() function, e.g.

select id, id_product, max(color) from colors
where color in ('BLUE','YELLOW')
group by id_product;