Handling joins - 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 2005 Forums

Transact-SQL (2005)

Handling joins

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-11-21 : 04:16:06

Hi,

I have four tables:

Employee    EmpDetail  EmpPbirth   EmpErlr
empid       empid      empid       empid
state       fnm        bplace      coinfo
            lnm        bcity       town   
            empdtid    emppobid    emperlrid

I have the following stored proc

CREATE PROCEDURE [dbo].[BringEmployeeData]
@EId int,
@language int
AS

SELECT distinct
emp.empid ,emp.state,
empdet.fnm, empdet.lnm,
empPob.bplace, empPob.bcity,
emperlr.coinfo, emperlr.town

FROM Employee as emp
INNER JOIN EmpDetail as empdet ON emp.empid = empdet.empid
LEFT OUTER JOIN EmpPbirth as empPob ON emp.empid = empPob.empid
LEFT OUTER JOIN EmpErlr as emperlr ON emp.empid = emperlr.empid
WHERE emp.empid = @EId
and empdet.lang = @language
and empPob.lang = @language
and emperlr.lang = @language

The data comes well when all the four tables are filled. However whenever there is no data in empPob and emperlr, this condition fails
--and empPob.lang = @language
--and emperlr.lang = @language

and no data appears. I cannot remove the condition as then data will be returned in multiple lang which is not desired.

What do i do to check the condition empPob.lang = @language if there is data and avoid it if there is no data

thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-21 : 04:23:40

[code]SELECT DISTINCT
emp.empid ,emp.state,
empdet.fnm, empdet.lnm,
empPob.bplace, empPob.bcity,
emperlr.coinfo, emperlr.town
FROM Employee AS emp
INNER JOIN EmpDetail AS empdet ON emp.empid = empdet.empid
LEFT OUTER JOIN EmpPbirth AS empPob ON emp.empid = empPob.empid AND empPob.lang = @language
LEFT OUTER JOIN EmpErlr AS emperlr ON emp.empid = emperlr.empid AND emperlr.lang = @language
WHERE emp.empid = @EId
AND empdet.lang = @language
[/code]

KH
[spoiler]Time is always against us[/spoiler]

lols
Posting Yak Master

174 Posts

Posted - 2007-11-21 : 04:31:48

thank you my friend

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources