left outer 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 2005 Forums

Transact-SQL (2005)

left outer join?

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-17 : 18:03:01

Hi there,

I have three tables
Job
JobID
WeightIn
WeightOut

Operator
OperatorID
NameFirst
NameLast

JobOperator
JobOperatorID
JobID
OperatorID

a job can be done with 1 or more operators
I want to be able to show user both assigned and unassigned operator on the front end on a certain job so I haev only parm being passed in @jobID.

How do I got about showing all the operators but somehow differentiate ones already assigned to job

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-17 : 18:07:13

Could you show us sample data for the three tables and provide the expected output of the query?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-17 : 18:08:37

Will this work?

SELECT [Operator] = OperatorFirstName + ' ' + COALESCE(OperatorMiddleName,'') + ' ' + OperatorLastName,
vo.OperatorID AS KeyField ,
hidSelectedYesNo =
( SELECT CASE
WHEN COUNT(*) > 0 THEN 1
ELSE 0
END
FROM VJobOperators vjo
WHERE vjo.JobID = @JobID
AND vjo.OperatorID = vo.OperatorID)
FROM dbo.VOperators AS vo

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-17 : 18:09:00

oops I meant to say
SELECT DISTINCT [Operator] = OperatorFirstName + ' ' + COALESCE(OperatorMiddleName,'') + ' ' + OperatorLastName,
vo.OperatorID AS KeyField ,
hidSelectedYesNo =
( SELECT CASE
WHEN COUNT(*) > 0 THEN 1
ELSE 0
END
FROM VJobOperators vjo
WHERE vjo.JobID = 1 AND vjo.OperatorID = vo.OperatorID)
FROM dbo.VOperators AS vo

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-17 : 18:09:28

Instead of asking us if something will work, why don't you try it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-17 : 18:26:02

Yes Almighty Goddess I tried it and it does work :O) That is what I meant to say I apologize.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources