SEO

It looks like you're using two different fields in the where clause which
is kind of bad form. Try (pm.global_id is null or pm.global_id = 2915). The thing
is, if you have matching records in project_members for every record in project
with the IDs, then pm.project_id will never be null. Thus, the first part
of your OR in the where clause is always false, so the validity of the second part,
"pm.global_id = 2915", is the only thing that has any bearing on the records
returned. And if you only have 3 - you're only going to get three back.

Err... reading what I said above, it sounds kind of lame. Using two different
fields in the where clause specifically for this kind of construct would
be the bad form thing Else, you are asking for unexpected results, such as
what you are getting now.

Jason

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailtogsql-admin-owner@postgresql.org]On Behalf Of D Kavan
Sent: Friday, August 05, 2005 3:32 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] outer joins



Hi,

Thanks for the reply. That didn't help him, but he asked me to post this.

There are 66 project id's in the project table and 3 rows in the
project_members table for global_id 2915, but it only returns 3. I would
think it should return 66 rows, with 63 of them having a null pm.project_id.

SELECT
p.project_id, pm.project_id
FROM project p
LEFT OUTER JOIN project_members pm ON p.project_id = pm.project_id
where (pm.project_id is null or pm.global_id = 2915)



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org