Skip to content
Advertisement

How to find missing data in table Sql

This is similar to How to find missing data rows using SQL? and How to find missing rows (dates) in a mysql table? but a bit more complex, so I’m hitting a wall.

I have a data table with the noted Primary key:

I have a products table with all products, a countries table, and a calendar table with all valid dates.

I’d like to find all countries, dates and products for which there are missing products, with this wrinkle: I only care about dates for which there are entries for a country for at least one product (i.e. if the country has NOTHING on that day, I don’t need to find it) – so, by definition, there is an entry in the history table for every country and date I care about.

I know it’s going to involve some joins maybe a cross join, but I’m hitting a real wall in finding missing data.

I tried this (pretty sure it wouldn’t work):

No Joy.

I tried this too:

also no luck. The CTE does get me every product on every date that there is also data, but the rest returns nothing.

Advertisement

Answer

I only care about dates for which there are entries for a country for at least one product (i.e. if the country has NOTHING on that day, I don’t need to find it)

So we care about this combination:

Then it’s just a matter of checking for existence:

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement