Outer Join that limits one side

I'm trying to retrieve a result set that uses an Outer Join, but I want to
limit the records in one of tables. The problem is when I insert the Where
condition, it doesn't show the other "Outer" records from the other table.
Here is the current SQL Syntax:
SELECT ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
FROM PROJECT_FAMILY PF INNER JOIN
PROJECT P ON PF.FamilyID = P.FamilyID LEFT OUTER JOIN
EMAIL_SIGNUP ES ON P.ProjectID = ES.ProjectID
WHERE ES.WebContactID = 1
When I remove the WHERE condition, it displays all the records from PROJECT,
which is what I want, but also all the EMAIL_SIGNUP records, which I don't
want. I want to limit the EMAIL_SIGNUP to one ID, which is the primary key.
The result should look like this:
1 (Project Name A) 1 (Family Name X)
NULL (Project NameB ) 2 (Family Name X)
1 (Project Name C) 3 (Family Name Y)
Instead it looks like this:
1 (Project Name A) 1 (Family Name X)
1 (Project Name C) 3 (Family Name Y)
I assuming I need to move the "ES.WebContactID = 1". Thanks.Try:
SELECT ES.webcontactid, P.projectname, P.projectid, PF.familyname
FROM project_family AS PF
INNER JOIN project AS P
ON PF.familyid = P.familyid
LEFT OUTER JOIN email_signup AS ES
ON P.projectid = ES.projectid
AND ES.webcontactid = 1
David Portas
SQL Server MVP
--|||As soon as you put a predicate in the where clause, which operates on the
"wrong" side of an outer join, you effectively destroy the "Outerness".
Think of it this way, The Join creates a temporarily constructed resultset
consisting of everything put together up to then, plus the records from the
new table being joined, using the join conditions... WEach Join repeats this
process, using only the conditions assiated with that specific join.
The where clause conditions, on the other hand, apply to the last
constructed resultset, after all joins have been done.
So before your where clause operates, all those record s were in there,
including the ones from PROJECT_FAMILY and PROJECT that had no counterparts
in EMAIL_SIGNUP - but for every one of those, the columns from EMAIL_SIGNUP
were NULL. then you say
Where ES.WebContactID = 1, and that eliminates all of them, because
ES.WebContactID Is NULL for all of them...
What you need to do is add this additional predicate condition in the Join
conditions.
Like THis:
Select ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
From PROJECT_FAMILY PF
Join PROJECT P
On PF.FamilyID = P.FamilyID
Left Join EMAIL_SIGNUP ES
On P.ProjectID = ES.ProjectID
And ES.WebContactID = 1
"jmhmaine" wrote:

> I'm trying to retrieve a result set that uses an Outer Join, but I want to
> limit the records in one of tables. The problem is when I insert the Where
> condition, it doesn't show the other "Outer" records from the other table.
> Here is the current SQL Syntax:
> SELECT ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
> FROM PROJECT_FAMILY PF INNER JOIN
> PROJECT P ON PF.FamilyID = P.FamilyID LEFT OUTER JOIN
> EMAIL_SIGNUP ES ON P.ProjectID = ES.ProjectID
> WHERE ES.WebContactID = 1
> When I remove the WHERE condition, it displays all the records from PROJEC
T,
> which is what I want, but also all the EMAIL_SIGNUP records, which I don't
> want. I want to limit the EMAIL_SIGNUP to one ID, which is the primary ke
y.
> The result should look like this:
> 1 (Project Name A) 1 (Family Name X)
> NULL (Project NameB ) 2 (Family Name X)
> 1 (Project Name C) 3 (Family Name Y)
> Instead it looks like this:
> 1 (Project Name A) 1 (Family Name X)
> 1 (Project Name C) 3 (Family Name Y)
> I assuming I need to move the "ES.WebContactID = 1". Thanks.|||jmhmaine wrote on Tue, 15 Mar 2005 07:35:02 -0800:

> I'm trying to retrieve a result set that uses an Outer Join, but I want to
> limit the records in one of tables. The problem is when I insert the Where
> condition, it doesn't show the other "Outer" records from the other table.
> Here is the current SQL Syntax:
> SELECT ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
> FROM PROJECT_FAMILY PF INNER JOIN
> PROJECT P ON PF.FamilyID = P.FamilyID LEFT OUTER JOIN
> EMAIL_SIGNUP ES ON P.ProjectID = ES.ProjectID
> WHERE ES.WebContactID = 1
You are limiting the results to only those that have a WebContactID value of
1. You need to add an OR for the NULL values where there is no matching
record.
SELECT ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
FROM PROJECT_FAMILY PF INNER JOIN
PROJECT P ON PF.FamilyID = P.FamilyID LEFT OUTER JOIN
EMAIL_SIGNUP ES ON P.ProjectID = ES.ProjectID
WHERE ES.WebContactID = 1 OR ES.WebContactID IS NULL
Dan|||No that just lists the all the records in Project, and webcontactid is NULL.
Josh.
"David Portas" wrote:

> Try:
> SELECT ES.webcontactid, P.projectname, P.projectid, PF.familyname
> FROM project_family AS PF
> INNER JOIN project AS P
> ON PF.familyid = P.familyid
> LEFT OUTER JOIN email_signup AS ES
> ON P.projectid = ES.projectid
> AND ES.webcontactid = 1
> --
> David Portas
> SQL Server MVP
> --
>|||Ignore this message. That worked thanks.
Josh.
"jmhmaine" wrote:
> No that just lists the all the records in Project, and webcontactid is NUL
L.
> Josh.
> "David Portas" wrote:
>|||jmhmaine,
For those records in Project which have no matching record in email_signup,
the value of webcontactid MUST BE NULL. There's no way around that... Thos
e
records don't HAVE a webContactID, cause that data item is in the
email_signup Table...
"jmhmaine" wrote:
> No that just lists the all the records in Project, and webcontactid is NUL
L.
> Josh.
> "David Portas" wrote:
>