Query help for the disadvantaged.

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)

Query help for the disadvantaged.

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-03 : 13:59:26

Working on a query that I am having difficulty with.

I have a Tables

TABLE JobDetails

JobTitle char(50),
JobID int

and

TABLE JobsActive

LocID int,
JobID int

I am working on a stored procedure and passing the @LocID var to it. I am trying to create a select with all the JobTitles but an addition column called 'Active'.

So if I have this data


JobDetails:
JobID       JobTitle
-----       --------
1            SalesPerson
2            Cashier
3            Shipper
4            Manager

JobsActive:

LocID       JobID
-----       -------
333           1
333           2
999           4
888           2

And I passed the @LocID of 333 This is what I need in return.


Result Set:

JobID      JobTitle          Active
------     --------          ------
1           Salesperson        1
2           Cashier            1
3           Shipper            0
4           Manager            0

Hopefully that makes sense. For the active it will be used in a Boolean expression, so I could care less about the 0 being returned, heck the '1' could even be the LocID.

Hope this makes sense.

Thanks!!!!!!!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-03 : 19:23:59

use LEFT JOIN

SELECT j.JobID, j.JobTitle, 
       Active = CASE a.JobID IS NOT NULL THEN 1 ELSE 0 END
FROM   JobDetails j LEFT JOIN JobsActive a
       ON  j.JobID = a.JobID
       AND a.LocID = @LocID

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

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-03 : 19:31:43

khtan,

Thanks for the Idea.

I just got this working,as you responded, but was using a temp table.

Your way is much better and I will use that!!!

Thanks a million.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources