complex query join checking that value does not exist

Question

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.

score 1 · Accepted Answer · edited May 23 '17 at 11:56

1

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
       )

edited May 23 '17 at 11:56

Community

1
1

answered Dec 21 '12 at 15:42

Simon Martin

4,203
7
56
93

That is exactly what I was hoping for. Let me test and get back to you. I had coded so many sub queries trying to get this to work. Arg, – htm11h Dec 21 '12 at 15:54
Returns an Error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS – htm11h Dec 21 '12 at 16:04
1

sorry... I *should have* removed the extra items in the sub select. I have revised my code. – Simon Martin Dec 21 '12 at 16:07
Ah that worked, Thanks where do I add the check for t2 Active = True though? – htm11h Dec 21 '12 at 16:09
1

I've added that to the code, it goes in the where clause as you would do normally – Simon Martin Dec 21 '12 at 16:12
I am having a problem getting the correct results returned. tb1 schedDate has a value for 11/12/2012 but returns no records, if I enter 11/01/2012 I get the correct records back. The 11/1/2012 value is a variable in the actual code, @rteDate. – htm11h Dec 21 '12 at 17:10
1

You could try casting the date to a date. Depends on what the data type is; are you trying to compare a string and a date for example – Simon Martin Dec 21 '12 at 18:00
Tried this: AND (CAST(schedDate AS DATE) = CAST(@targetDate AS DATE)) and I am getting the same results. Even in the query editor. – htm11h Dec 21 '12 at 18:51
I thought the schedDate clause was working in your original select? Can you check that you can filter on just that field with expected values. I'm thinking that there's something strange such as a US date format not matching a UK format - so you may be passing 11/12/2012 but it is not matching 12/11/2012. – Simon Martin Dec 21 '12 at 19:50
If I use a literal date 11/12/2012 it works, it fails with the variable in place of the actual date unless the date does NOT match an actual date in the table. All date formats are mm/dd/yyyy. – htm11h Dec 21 '12 at 20:14
Still sounds like a data type conversion issue; are you storing dates with full date time i.e. mm/dd/yyyy HH:MM:SS and so the where isn't matching when you pass a date but it does when you pass the literal. – Simon Martin Dec 21 '12 at 23:39
The issue appears to be the fact that there is no initial records for schedDate. If the inside query returns Null, then I get no results. If at least one record exists for the schedDate I get the correct results. – htm11h Jan 03 '13 at 16:07
sorry schedDate will never be null, I was refering to a NULL match on any drIDs. And I have tried adding (drID IS NULL OR drID = '') it also resulted in nothing being returned. So if no drIDs exist at all on a given date, as if this is the first check prior to an insert, the query fails. If there is already a record with a drID on the submitted date, I get the correct results. But I need to test the first case, before adding the record. – htm11h Jan 03 '13 at 17:23
Maybe a NOT EXISTS clause instead of the NOT IN would work for you http://stackoverflow.com/a/11074428/96505 – Simon Martin Jan 03 '13 at 19:14

complex query join checking that value does not exist

1 Answers1

Linked