Date: 01/15/08 (MySQL Communtiy) Keywords: mysql, sql A table exists where the same id can be issued more than once. Each id has an accompanying color. Code: CREATE TABLE `xorder` ( `id` INT( 3 ) NOT NULL , `color` VARCHAR( 20 ) NULL , ) ENGINE = MYISAM Code: INSERT INTO `xorder` ( `id` , `color` ) VALUES ( '1', 'green' ), ( '2', 'red' ), ( '3', 'black' ), ( '1', 'yellow' ), ( '2', 'blue' ), ( '1', 'orange' ), ( '2', 'white' ), ( '2', 'purple' ), ( '4', 'blue' ), ( '5', 'gray' ) I need to select all ids where there is no duplicate id having the color 'blue'. Code: id color 1 green 1 yellow 1 orange 2 red 2 blue 2 white 2 purple 3 black 4 blue 5 gray So the id's I need returned from this data are 1, 3 and 5, ...since no associated orders have the color blue. I could loop through the orders and use looped mysql queries but I really don't want to go there. Any suggestions on how this could be done in a single query?
|