Outer Join not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Outer Join not working

Author

Topic

gstroud
Starting Member

2 Posts

Posted - 2006-06-15 : 21:29:28

Hello

I'm not that good with ansi sql (I'm use to the *= way) so please excuse me. My problem is I'm trying to return a result set regardless of values existing in the ProviderPatient table. It works if I enter a month and year that has values in the ProviderPatient table. What am I doing wrong?

Select tblJoinFacilityContact.ContactFacilityNo,
ProviderPatient.PctASA,
ProviderPatient.PctBetaBlocker,
ProviderPatient.TimeToThrombolytics,
ProviderPatient.TimeToPCA

From tblJoinFacilityContact
Left Outer Join ProviderPatient on tblJoinFacilityContact.ContactFacilityNo = ProviderPatient.ContactFacilityNo

Where tblJoinFacilityContact.MedicalFacilityID = 2
and ProviderPatient.Act_Month = 5
and ProviderPatient.Act_Year = 2006;

Thanks in advance.

Greg

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-15 : 22:21:38

You are defeating the purpose of a left join by specifying values for month and year in your where clause, bacause that will prevent it returning rows where there is no match for ProviderPatient.

If you only want rows from ProviderPatient for that month and year, you should move the condition to the join clause.


Select
	a.ContactFacilityNo,
	b.PctASA, 
	b.PctBetaBlocker, 
	b.TimeToThrombolytics, 
	b.TimeToPCA
From
	tblJoinFacilityContact a
	Left Outer Join 
	ProviderPatient b
	on
	a.ContactFacilityNo = b.ContactFacilityNo and
	b.Act_Month = 5 and
	b.Act_Year = 2006
Where
	a.MedicalFacilityID = 2

CODO ERGO SUM

gstroud
Starting Member

2 Posts

Posted - 2006-06-15 : 22:37:04

Thank you Michael. I looked at this and didn't see it.

Greg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources