The title is not clear as I don't know how to express the problem in one sentence.

I have a table named companies, looks like this:

And then I have a table where I record the last crawl operations I made. The idea is having a script that can restart crawling everyday without recrawling companies that have been done.

Crawl_Settings

I thought about making a query that would get me all the companies that have a last crawl date that is not today.

Select * from companies where symbol,exchange in (
  select symbol,exchange from crawl_settings where date != today
)

I think that this query will work, but won't get me the companies that have never been crawled. Expected result:

explanation: AMZN has an old crawl date, MCSFT has never been crawled.

Actual result:

CodePudding user response：

You can use an anti-join to get the rows you want. For example:

select c.*
from companies c
left join crawl_settings s on s.symbol = c.symbol 
                          and s.exchange = c.exchange
                          and s.date = today
where s.symbol is null

CodePudding user response：

Impaler's solution is objectively better, because NOT IN has some finicky behavior. But just putting it out there that you were very close to being on the right track.

Just tweak where your inequality is.

Select * from companies where symbol,exchange not in (
  select symbol,exchange from crawl_settings where date = today
)