List:General Discussion« Previous MessageNext Message »
From:Murad Nayal Date:December 10 2002 2:23am
Subject:Re: help on NOT EXISTS SQL query
View as plain text  

I think i found a way to do this: in case anybody is interested:

select customer from purchases group by customer having sum(case when
purchase = 'freezer' then 1 else 0 end) = 0;

I am finding that SQL is trickier (and more powerful) than I thought
originally!!

Murad Nayal wrote:
> 
> 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
> > >
> > >
> > >
> > >
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