January 24, 2008 at 11:02 am
I need to find certain select list values in which two of the fields, in combination, are not in a specified sub-query in the WHERE. T-SQL allows me to only use a single field as in:
. . .
WHERE
. . .
AND i1.employee_id NOT IN (SELECT i2.employee_id FROM dbo.ivr i2
WHERE i2.plannbr = '26643' and i2.storenbr = 69529)
OR
AND i1.servicedate NOT IN (SELECT i2.servicedate FROM dbo.ivr i2
WHERE i2.plannbr = '26643' and i2.storenbr = 69529)
But what I need is is to find where the combination of the employee_id and servicedate, together, are not in the sub-query. Please advise!
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
January 24, 2008 at 11:12 am
Try switching this over to an EXISTS instead:
WHERE
....
AND
NOT EXISTS ( select *
from dbo.ivr i2
WHERE i2.plannbr = '26643' and i2.storenbr = 69529
and i1.employee_ID=i2.employee_ID
and i1.servicedate=i2.servicedate
)
AND
...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 24, 2008 at 11:15 am
EDITED: Incorrectly used INNER join when LEFT is appropriate.
Matt's suggestion OR use the left join method ...
FROM
....
LEFT JOIN dbo.ivr i2
ON i2.plannbr = '26643' and i2.storenbr = 69529
AND i2.employee_id = i1.employee_id
AND i2.servicedate = i1.servicedate
WHERE
i2.employee_id IS NULL
AND i2.servicedate IS NULL
There's almost always more than one way to skin a cat! I'm not sure which, if either one, would be more efficient.:D
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply