Simple Right Join? - 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 2008 Forums

Transact-SQL (2008)

Simple Right Join?

Author

Topic

webwired
Starting Member

3 Posts

Posted - 2009-03-20 : 08:55:37

Hi, I'm attempting my first right join and its not going very good so far... What I'm trying to accomplish is list ALL of the CertificationID and CertificationName regardless of the WHERE condition and then only the EmployeeCertificationDateExpires and EmployeeCertificationDocument of those that do meet the WHERE condition...

Ok, so here are the two tables...
Certifications
--CertificationID
--CertificationName
--CertificationLifeSpan

EmployeeCertifications
--EmployeeID
--CertificationID
--EmployeeCertificationDateAttended
--EmployeeCertificationDateExpires
--EmployeeCertificationDocument

And here is the Stored Procedure that I'm trying to use...


ALTER PROCEDURE dbo.GetEmployeeCertifications
	(
	@EmployeeID int
	)
As
  Set NoCount On;
  
SELECT 
EmployeeCertifications.EmployeeCertificationDateExpires, 
EmployeeCertifications.EmployeeCertificationDocument, 
Certifications.CertificationID, 
Certifications.CertificationName
FROM EmployeeCertifications RIGHT JOIN Certifications 
ON EmployeeCertifications.CertificationID = Certifications.CertificationID
WHERE EmployeeCertifications.EmployeeID = @EmployeeID ;

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-20 : 09:01:57

Your where condition will be eliminating any of the RIGHT JOIN results that didn't have matches.

-- The WHERE condition is evaluated after all the joins and because the EmployeeCertifications.<whatever> will be NULL for no matches in the right join then your WHERE clause will bin them.

Try:


WHERE
    EmployeeCertifications.EmployeeID = @EmployeeID
    OR
    EmployeeCertifications.[EmployeeId] IS NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webwired
Starting Member

3 Posts

Posted - 2009-03-20 : 09:09:32

Thank You very much Transact Charlie ... It worked perfectly...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources