List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 16 2001 12:03am
Subject:Re: [SQL] Logical grouping (maybe not so logical)
View as plain text  
At 3:43 PM -0700 10/15/01, Rodney Broom wrote:
>I have a table with data like this:
>
>   mysql> select * from jack;
>   +------+--------+
>   | id   | data   |
>   +------+--------+
>   |    1 | red    |
>   |    1 | blue   |
>   |    2 | blue   |
>   |    2 | orange |
>   |    3 | orange |
>   |    3 | green  |
>   +------+--------+
>
>
>I want all 'id's that don't have a 'data' column of "red". One might 
>think to use this:
>
>   select distinct(id) from jack where data != 'red'
>
>But of course I get the second record in this table with id=1 and 
>data=blue. The only ids that I want would be 2 and 3. Any thoughts?

Select id values for red rows:

CREATE TEMPORARY TABLE t
SELECT DISTINCT(id) FROM jack WHERE data = 'red';

Select rows not matching any of those values:

SELECT DISTINCT(jack.id)
FROM jack LEFT JOIN t ON jack.id = t.id
WHERE t.id IS NULL;

>
>
>---
>Rodney Broom
>Programmer: Desert.Net
>
>Spam filter: sql


-- 
Paul DuBois, paul@stripped
Thread
[SQL] Logical grouping (maybe not so logical)Rodney Broom16 Oct
  • Re: Logical grouping (maybe not so logical)Carl Troein16 Oct
  • Re: Logical grouping (maybe not so logical)Rodney Broom16 Oct
  • Re: [SQL] Logical grouping (maybe not so logical)Paul DuBois16 Oct
  • Re: [SQL] Logical grouping (maybe not so logical)Harald Fuchs16 Oct
  • RE: [SQL] Logical grouping (maybe not so logical)Jindo Soul16 Oct