List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:August 1 2000 10:20pm
Subject:Re: Select w/o subselect?
View as plain text  
In the last episode (Aug 01), Christopher W. Curtis said:
> I'm verily a SQL novice, but I think I've hit a MySQL roadblock ...
> I have a table, such as:
>  path       state
>  ----       -----
> foo         0
> foo         0
> foo         1
> bar         1
> bar         1
> I need to write a query that will select each path for which every
> associated state is 1, such that the result from the data above is
> "bar" - every state of bar is '1'.  The documentation I've found
> leads me to believe that something like this would work:
> select path as A,state from table where path != (select path,state
> from table where path=A and state != '1' limit 1)
> but MySQL doesn't seem to like subselects.  Is there an alternate
> method of writing this in MySQL's SQL implementation?

A quick solution that only works because 'state' is a 1/0 boolean:

select path, min(state) mst from table group by path having mst=1;
	Dan Nelson
Select w/o subselect?Christopher W. Curtis1 Aug
  • Re: Select w/o subselect?Dan Nelson1 Aug
  • Re: Select w/o subselect?Steve Ruby1 Aug
  • Re: Select w/o subselect?Christopher W. Curtis1 Aug
    • Re: Select w/o subselect?Ken Yiem2 Aug
  • Re: Select w/o subselect?Christopher W. Curtis1 Aug
RE: Select w/o subselect?Quentin Bennett1 Aug
Re: Select w/o subselect?Christopher W. Curtis1 Aug