CODEWITHSUNDEEP
sqlsql-servermissing-data
Sven
Sven

Reputation: 47

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.

TableA

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

Table2
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

Upvotes: 1

Views: 103

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 2

Related Questions