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
> > >
> > >
> > >
> > >
