CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
htm11h
htm11h

Reputation: 1779

complex query join checking that value does not exist

I am struggling with a great challenge of a query. I have two tables, first has

Tb1 drID schedDate rteID

Second has:

Tb2 drID FName LName Active

Tb1 drID must be checked for Null or blank and match on schedDate and drID can not have any values that match Tb2.drID for date selected, checking for Null and '' essentially do this.

SELECT drID, schedDate, rteID
FROM            Tb1
WHERE        (drID IS NULL OR drID = '') AND (schedDate = 11 / 1 / 2012)

From all of this I need to return from TB2 drID, Fname, LName Where Active = True and drID does not exist on any record in tb1 for the schedDate selected.

There are many tb1 rteID records for any possible date.

Thank you for any help on this and huge Holiday Thank You.

Upvotes: 0

Views: 654

Answers (1)

Simon Martin
Simon Martin

Reputation: 4231

Can you make your select statement a subquery for example:

SELECT drID, Fname, LName
FROM   TB2
WHERE  Active = True 
 AND   drID NOT IN (
       SELECT drID
       FROM   Tb1
       WHERE  (drID IS NULL OR drID = '')
        AND  (schedDate = 11 / 1 / 2012)
       )

Edit
To handle the case that the schedDate is null then

SELECT drID, Fname, LName
FROM   TB2
WHERE  Active = True 
 AND   drID NOT IN (
       SELECT drID
       FROM   Tb1
       WHERE  (drID IS NULL OR drID = '')
        AND  (schedDate = @yourDate OR schedDate IS NULL)
       )

Edit 2
To handle the case that the drID is null then you can use the NOT EXISTS approach as highlighted in this SO post about NOT IN vs NOT EXISTS

SELECT drID, Fname, LName
FROM   TB2
WHERE  Active = True 
 AND   NOT EXISTS (
       SELECT drID
       FROM   Tb1
       WHERE  (schedDate = @yourDate) 
        AND   Tb1.drID = TB2.drID
       )

Upvotes: 1

Related Questions