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

Outer join for two tables

Hi guys,

Please Help!

I am using Excel's VBA in order to retrieve sql Data.

I have three tables of the "my Company" DataBase (sql server 2000):

Table1, Table2, Table3

I need to join:

Table1.FieldXX=Table2.FieldYY

Table1.FieldWW= Table3.FieldZZ

In both cases, I should retrieve all data from Table1, even if Table2 or Table3 don't have the correspondent entries.

I am trying to use the code below, but getting "sql syntax error":

FROM myCompany.dbo.Table1 Table1

LEFT OUTER JOIN myCompany.dbo.Table2 Table2 ON (Table1.FieldXX=Table2.FieldYY)

AND

LEFT OUTER JOIN myCompany.dbo.Table3 Table3 ON (Table1.FieldWW= Table3.FieldZZ)

I will appreciatte any help.

Thanks in advance,

Aldo.Hi Guys,

This is the code working properly:

FROM myCompany.dbo.Table1 Table1

LEFT OUTER JOIN myCompany.dbo.Table2 Table2 ON Table1.FieldXX= Table2 .FieldYY

LEFT OUTER JOIN myCompany.dbo.Table3 Table3 ON Table1.FieldWW= Table3.FieldZZ

Aldo.sql

other computer can't connect to my sql server 2000

some of my colleagues need to connect to my database to retrieve some report
s
but they can't connect to my computer. i didn't set any specific passwords a
s
i'm using my window account ( username - Administrator , password - ** )
during the nistallation but the system always show "Connection to Database
Server Fail! Login Failed For User 'sa'" error message whenever they try to
connect to my database. Although i changed the user id in the .ini file to
Administrator also the same. Surely i did wrong somewhere. Please help guys.
Thanks a lot.What version of SQL Server?
Is SQL Authentication enabled?
Why are others attempting to connect using the 'sa' account? (Very BAD
idea!)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andres" <Andres@.discussions.microsoft.com> wrote in message
news:2090B026-13E1-43E8-BB84-FF34D1AB8840@.microsoft.com...
> some of my colleagues need to connect to my database to retrieve some
> reports
> but they can't connect to my computer. i didn't set any specific passwords
> as
> i'm using my window account ( username - Administrator , password - ** )
> during the nistallation but the system always show "Connection to Database
> Server Fail! Login Failed For User 'sa'" error message whenever they try
> to
> connect to my database. Although i changed the user id in the .ini file to
> Administrator also the same. Surely i did wrong somewhere. Please help
> guys.
> Thanks a lot.