Query question

[Link82]

Hi Guys,

Kind of stuck on a query here.

select		*--, c.last_name + ', ' + c.first_name as name
from		dbo.tblLevelOneApprover a

inner join	dbo.tblLevelTwoApproverToLevelOneApprover b
on			convert(int, b.level_two_emplid) != convert(int, @emplid)

where convert(int, a.emplid) = convert(int, b.level_one_emplid)

I'm trying to get all level one managers who are not mapped to a particular level 2 manager. For example, let's call this table dbo.tblLevelTwoApproverToLevelOneApprover with following entries:

level_two_emplid | level_one_emplid
stan | susan
al | freddy
max | susan
karen | jeff
bob | freddy

So what I'm trying to do is, when I try to run the above query with stan's id, I should get back only jeff and freddy (people who are not mapped to him). Another example is if we run bob, we should get back jeff and susan.

In the above query, I have the line on convert(int, b.level_two_emplid) != convert(int, @emplid)
which minuses susan once, but since she's repeated with another level 2 manager, her name still shows up. So how do I say don't return me the names of the people that you map to.

Am I missing something ridiculously simple here?

[sknake]

You're taking the wrong approach.. Conceptually you should be doing

Declare @level2mgrId int
Set @level2mgrId  = 5

Select *
From Level1
Where
NOT EXISTS
(
  Select *
  From Level2
  Where Level2.ParentManagerId = Level1.ManagerId and Level2.ManagerId = @level2mgrId
)

Let me know if that wasn't what you were after

[cgyrob]

I don't see where you are getting the alias 'c'

Besides that i think you are making it more difficult then it has to be.

select *
from dbo.tblLevelOneApprover a, dbo.tblLevelTwoApproverToLevelOneApprover b
Where convert(int, b.level_two_emplid) <> convert(int, a.emplid)

[sknake]

cgyrob -- Check out these threads:
http://www.daniweb.com/forums/thread203649.html
http://www.daniweb.com/forums/thread202905.html

They give a little more background on the database

[cgyrob]

@sknake - thanks, I didn't realize there were multiple threads relating to this db.

[sknake]

I wouldn't have either had I not participated :P

[cgyrob]

I actually remember reading both of these threads and had commented on one of them but I still did not correlate them.

[Link82]

Hi sknake and cgyrob,

Thanks for the help. I apologize for no additional details. My head was too into it and I should have looked at the post after clearing it.

But sknake knew exactly wh at I needed! And yes, not exists is what I should've considered. Can't believe I wasn't even thinking using 'not exists'. Don't think I've had to so far, but learning new stuff, so that's cool :)