Re: NOT HAVING clause?

Michael Glaesemann wrote:
>
> On Jan 24, 2006, at 20:00 , Alban Hertroys wrote:
>
>> Though this does give the right results, I would have liked to be
>> able to use NOT HAVING. Or is there a way using HAVING that would
>> give the same results? I'm quite sure HAVING sort_order <> 1 doesn't
>> mean the same thing.
>
> Why are you so sure? It seems to me that NOT HAVING sort_order = 1 and
> HAVING sort_order <> 1 would mean semantically the same thing. Can you
> show that HAVING sort_order <> 1 gives incorrect results?

There's a difference in meaning. By NOT HAVING sort_order = 1 I mean
there is no record in the grouped records that has sort_order = 1. In
contrast HAVING sort_order <> 1 means there is a record in the group
with a sort_order other than 1, even if there's also a sort_order = 1 in
the grouped records.

To illustrate, say we have sort_orders 1,2,3,4,5:
- NOT HAVING sort_order = 1 would result false
- HAVING sort_order <> 1 would result true

If we'd have 2,3,4,5:
- NOT HAVING sort_order = 1 would result true
- HAVING sort_order <> 1 would result true

If we'd have 1 only:
- NOT HAVING sort_order = 1 would result false
- HAVING sort_order <> 1 would result false

But it seems HAVING can't be applied to columns not in the group by or
an aggregate. No idea why that might be...

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

//Showing your Vision to the World//