MS SQL

MS SQL RSS

Query question

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Jul 2008

Posts: 24

Reputation: Link82 is an unknown quantity at this point

Solved Threads: 0

Link82

Offline

Newbie Poster

Query question

Jul 21st, 2009

Hi Guys,

Kind of stuck on a query here.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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)
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?

•

Join Date: Feb 2009

Posts: 3,215

Reputation: sknake has much to be proud of

Solved Threads: 573

sknake

Offline

.NET Enthusiast

Re: Query question

Jul 21st, 2009

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
DECLARE @level2mgrId INT
SET @level2mgrId  = 5
 
SELECT *
FROM Level1
WHERE
NOT EXISTS
(
  SELECT *
  FROM Level2
  WHERE Level2.ParentManagerId = Level1.ManagerId AND Level2.ManagerId = @level2mgrId
)
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

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Sep 2008

Posts: 118

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: Query question

Jul 21st, 2009

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.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT *
FROM dbo.tblLevelOneApprover a, dbo.tblLevelTwoApproverToLevelOneApprover b
WHERE convert(INT, b.level_two_emplid) <> convert(INT, a.emplid)
select *
from dbo.tblLevelOneApprover a, dbo.tblLevelTwoApproverToLevelOneApprover b
Where convert(int, b.level_two_emplid) <> convert(int, a.emplid)

Last edited by cgyrob; Jul 21st, 2009 at 6:20 pm.

•

Join Date: Feb 2009

Posts: 3,215

Reputation: sknake has much to be proud of

Solved Threads: 573

sknake

Offline

.NET Enthusiast

Re: Query question

Jul 21st, 2009

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

Last edited by sknake; Jul 21st, 2009 at 6:22 pm.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Sep 2008

Posts: 118

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: Query question

Jul 21st, 2009

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

•

Join Date: Feb 2009

Posts: 3,215

Reputation: sknake has much to be proud of

Solved Threads: 573

sknake

Offline

.NET Enthusiast

Re: Query question

Jul 21st, 2009

I wouldn't have either had I not participated

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Sep 2008

Posts: 118

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: Query question

Jul 21st, 2009

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

•

Join Date: Jul 2008

Posts: 24

Reputation: Link82 is an unknown quantity at this point

Solved Threads: 0

Link82

Offline

Newbie Poster

Re: Query question

Jul 22nd, 2009

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