Sorry, worst Subject I've ever come up with, but this is one of those "I
haven't got a clue how to describe" emails ...
Simple query:
SELECT distinct s.gid, s.created, i.title
FROM status s LEFT JOIN images i ON (s.gid = i.gid AND i.active), personal_data
pd, relationship_wanted rw
WHERE s.active AND s.status != 0
AND s.gid = 17111
AND (s.gid = pd.gid AND pd.gender = 0)
AND (s.gid = rw.gid AND rw.gender = 0 );
Produces:
gid | created | title
-------+------------------------+--------
17111 | 2000-10-19 15:20:46-04 | image1
17111 | 2000-10-19 15:20:46-04 | image2
17111 | 2000-10-19 15:20:46-04 | image3
(3 rows)
Great, what I expect ...
But:
SELECT distinct s.gid, s.created, count(i.title) AS images
FROM status s LEFT JOIN images i ON (s.gid = i.gid AND i.active), personal_data
pd, relationship_wanted rw
WHERE s.active AND s.status != 0
AND s.gid = 17111
AND (s.gid = pd.gid AND pd.gender = 0)
AND (s.gid = rw.gid AND rw.gender = 0 )
GROUP BY s.gid, s.created;
Produces:
/tmp/psql.edit.70.62491: 7 lines, 353 characters.
gid | created | images
-------+------------------------+--------
17111 | 2000-10-19 15:20:46-04 | 15
(1 row)
So why is it counting 12 more images then are actually found/exist:
testdb=# select title from images where gid = 17111;
title
--------
image1
image3
image2
(3 rows)
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
