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@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx]On Behalf Of D Kavan Sent: Friday, August 05, 2005 3:32 PM To: pgsql-admin@xxxxxxxxxxxxxx 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
