Subselect help

    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.

    SQL to build table:

    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?

    Source: http://community.livejournal.com/mysql/122903.html

« I'm such a lummox || ORDER BY with GROUP BY »


antivirus | apache | asp | blogging | browser | bugtracking | cms | crm | css | database | ebay | ecommerce | google | hosting | html | java | jsp | linux | microsoft | mysql | offshore | offshoring | oscommerce | php | postgresql | programming | rss | security | seo | shopping | software | spam | spyware | sql | technology | templates | tracker | virus | web | xml | yahoo | home