This is sort of a feature request, I suppose. I solved my problem, but
"NOT HAVING" seems to match better with the desired result or the way
you phrase the question in your mind, if that makes any sense...
I was hoping to write a query rather short by using a "NOT HAVING"
clause. The documentation didn't specify that, and trying it resulted in
a syntax error indeed...
My data consists of a series of images related to an object. There
should be at least one image per object with sort_order = 1. I want to
find all objects that don't match this criterium.
I have these tables (clipped a bit):
CREATE TABLE image (
image_id SERIAL PRIMARY KEY,
object_id INTEGER NOT NULL REFERENCES object MATCH FULL,
sort_order SMALLINT NOT NULL DEFAULT 1
);
CREATE TABLE object (
object_id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
This is what I want, but isn't a valid query:
SELECT object_id
FROM image
GROUP BY object_id
NOT HAVING sort_order = 1;
It is wonderfully short, one of the reasons I like this.
I could write this as:
SELECT object_id
FROM object
WHERE NOT EXISTS (
SELECT object_id
FROM image
WHERE sort_order = 1
AND object_id = object.object_id
);
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.
What is the general opinion on this from the developers? Did I just have
one of those wild and ridiculous ideas? :P
Regards,
--
Alban Hertroys
alban@xxxxxxxxxxxxxxxxx
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//
