Sven Sven - 1 year ago 72
SQL Question

SQL Server: Find rows rows in Table1 not in Table2 but need data from tables

I need to find missing rows, however, I need data from BOTH tables to be returned. I checked google but did not find a similar question.


thetime real-time
1 1 pm
2 5 pm
3 7 pm
4 9 pm
5 11 pm

thedate transaction_num thetime
1/1/2000 111 1
1/1/2000 111 4
1/1/2000 111 5
2/1/2000 111 2
2/1/2000 111 4
2/1/2000 222 1
2/1/2000 222 5

I need to select the date and transaction_num from Table2 that do not have a time in Table1 so the result from the select statement should have the date and trnsaction number for the missing times not in table2:

thedate transaction_num thetime
1/1/2000 111 2
1/1/2000 111 3
2/1/2000 111 1
2/1/2000 111 3
2/1/2000 111 5
2/1/2000 222 2
2/1/2000 222 3
2/1/2000 222 4

This is the code I have but it is giving me a multi-part binding error:

select t2.thedate, t2.transaction_num, t1.thetime
from table2 t2
where not exists(select t1.thetime
from table1 t1
where t2.thetime = t1.thetime)

Does anyone know how to solve this or can point me to an answer?
Most questions in stack overflow for missing rows involve returning data from one table but I need it for 2 tables.

Thank you

vkp vkp
Answer Source

It seems all the transaction_nums on all dates should have all the times associated with them. Else it would be treated as missing.

To do this, you can initially cross join the distinct date and transaction_num from table2 and thetime from table1. Then left join on this derived table to get the missing rows.

select tt.thedate, tt.transaction_num,tt.thetime
    from (
          select * from (
         (select distinct thedate,transaction_num from table2) a cross join
         (select distinct thetime from table1) b
        ) tt
         left join table2 t2 on t2.transaction_num=tt.transaction_num and t2.thetime=tt.thetime and tt.thedate=t2.thedate
where t2.transaction_num is null and t2.thedate is null and t2.thetime is null

Sample Demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download