Re: outer joins

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(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of D Kavan
Sent: Friday, August 05, 2005 3:32 PM
To: pgsql-admin(at)postgresql(dot)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