List:General Discussion« Previous MessageNext Message »
From:Murad Nayal Date:December 9 2002 6:40pm
Subject:Re: help on NOT EXISTS SQL query
View as plain text  
Thanks Adolfo, 

this actually won't quite do the trick though. I should have been a bit
more specific. the query actually comes up in the context of
computational genomics. a similar, perhaps more familiar problem would
be something like this:

table

id customer purchase
1  c1       microwave
2  c1       car
3  c1       freezer
4  c2       car
5  c2       microwave
6  c3       car
7  c3       CD player

etc.

the idea is to pull out all the customers who have never purchased say a
freezer:

if you do

select customer from table where purchase != "freezer"

you'll get all the INSTANCES of customer purchasing something other than
a freezer. i.e. you'll get c1,c2,c3. although c1 did purchase a freezer.

my best guess of how to do this in SQL was

select distinct t1.customer from table as t1 where not exists (select
NULL from table as t2 where t1.customer = t2.customer and t2.purchase =
'freezer')

- does that look about right for the purpose I mentioned?

- now how do you do that without the subquery (especially considering
that the performance of the subquery will probably be horrible)

many thanks
Murad

Adolfo Bello wrote:
> 
> SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.field1=t2.field2
> WHERE t2.field2 IS NULL
> 
> > -----Original Message-----
> > From: murad@stripped
> > [mailto:murad@stripped] On Behalf Of Murad Nayal
> > Sent: Monday, December 09, 2002 11:38 AM
> > To: MySQL List
> > Subject: help on NOT EXISTS SQL query
> >
> >
> >
> >
> > Hello all,
> >
> > I need to run query like (in mysql < ver.4):
> >
> > select * from table1 as t1 where not exists (select NULL from
> > table2 as t2 where t1.field1 = t2.field1)
> >
> > I know you can emulate an 'exists' subquery with a join. but
> > I just can't think of a way to emulate a 'not exists' without
> > a subquery. probably due to my limited sql experience. any hints?
> >
> > thanks a lot
> > Murad
> >
> > BTW: when do you think mysql 4.1 would be stable enough for
> > robust use (not necessarily mission critical).
> >
Thread
help on NOT EXISTS SQL queryMurad Nayal9 Dec
  • RE: help on NOT EXISTS SQL queryAdolfo Bello9 Dec
    • Re: help on NOT EXISTS SQL queryMurad Nayal10 Dec
    • Multiple MySQL db's on one machinetmb10 Dec
      • Re: Multiple MySQL db's on one machinePaul DuBois10 Dec
        • Re: Multiple MySQL db's on one machineMichael T. Babcock10 Dec
    • Multiple MySQL db's on one machinetmb10 Dec
    • Re: help on NOT EXISTS SQL queryMurad Nayal10 Dec
  • Re: help on NOT EXISTS SQL queryMurad Nayal10 Dec
  • Re: help on NOT EXISTS SQL queryiConnect \(Berlin\) Stefan Hinz10 Dec
Re: Multiple MySQL db's on one machineJan Steinman10 Dec