Re: outer joins

On Fri, 5 Aug 2005, D Kavan wrote:

>
> 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)

That's not what that query does.

That query matches up rows in project with rows in project_members based
on project_id and then restricts to only those projects that didn't match
or which matched to global_id=2915.

I think what was desired can be gotten with something like:

select p.project_id, pm.project_id
from project p left outer join project_members pm on
(p.project_id=pm.project_id and pm.global_id = 2915)

Which I believe will extend a project with no matching global_id=2915 row
with nulls.