Ad:

MS SQL Discussion Thread
Marked Solved
Views: 532

Jul 21st, 2009

Query question

Expand Post »

Hi Guys,

Kind of stuck on a query here.

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?

Similar Threads

SQL Query question over multiple tables in Oracle
how to generate a mysql query from a english question in MySQL
how to generate a mysql query from a english question #1 in Java
File and Printer Sharing Query in Networking
Select Query Problem in access in MS Access and FileMaker Pro

Link82

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

24 posts
since Jul 2008

Permalink

Jul 21st, 2009

Re: Query question

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

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

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Jul 21st, 2009

Re: Query question

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.

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.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Jul 21st, 2009

Re: Query question

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.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Jul 21st, 2009

Re: Query question

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

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Jul 21st, 2009

Re: Query question

I wouldn't have either had I not participated

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Jul 21st, 2009

Re: Query question

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

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Jul 22nd, 2009

Re: Query question

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

Link82

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

24 posts
since Jul 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Set Warnings Off

Next Thread in MS SQL Forum Timeline: Just a bit of help required, newbie here

DaniWeb Message

Cancel Changes

User Name
Password