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

thanks for the feedback. I think I probably misstated my problem. I just
emailed a more explicit example of the sort of thing I am trying to do.
for the sake of completeness I'll reproduce it here: 

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

the goal is to find all customers that have never bought a freezer.

am I correct in interpreting your suggestion, applied to this case, as
the query:

select customer from purchases where purchase != "freezer" is null

i tried and it returned zero rows. probably because purchase != freezer
is either true or false and neither value is null!

what am i missing?

Murad


"Stefan Hinz, iConnect (Berlin)" wrote:
> 
> Dear Murad,
> 
> > 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?
> 
> You have probably tried something like SELECT ... WHERE <condition> IS NOT
> NULL. To emulate a "not exists" subselect, you would use SELECT ... WHERE
> <condition> IS NULL.
> 
> > BTW: when do you think mysql 4.1 would be stable enough for robust use
> 
> As I hear, MySQL 4.1-alpha will be released very soon, probably in January.
> My guess for MySQL 4.1-gamma (the release declared as stable, meaning there
> are lots of installations in production environments that have proven
> stable) is August 2003. Any other guesses? Monty? ;-)
>
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