CODEWITHSUNDEEP
sqlsql-servert-sql
oknevermind
oknevermind

Reputation: 111

Get all rows that are not exist in the appropriate table depending on the date in sql

I have two tables: TableA and TableB (as in the following picture): enter image description here

The result should be as in the following figure:

enter image description here

What is the best way to get result (as in the table Result) using mssql query?

Thanks.

Upvotes: 0

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If I understand correctly, you want the date/value pairs that don't exist.

Generate the list of all date/value pairs using a cross join. Then filter out the ones you don't want:

select b.value, d.date
from tableb b cross join
     (select distinct date from tablea a) d
where not exists (select 1 from tablea a where a.date = d.date and a.value = b.value)

Upvotes: 2

Related Questions