How to join 2 tables and fill non matching entries with a date

Question

A company buys parts from other companies. To not overload their servers, every 5 minutes the price of 1 part is checked to see if a part has changed price. So a table contains the last time a part for a company has been checked.

Table companies

id	name
1	ACME
2	Stark industries

Table parts

id	name
10	Raspberry PI
11	Motherboard model X4282

Table last_checked

id	company_id_fk	part_id_fk	date_last_checked
20	1	10	2022-01-01 08:00
21	1	11	2022-01-01 10:00
22	2	10	2022-01-01 09:00
23	2	11	2022-01-01 07:00

If I now were to query last_checked I could find out for every company what the part was checked longest ago (in this case the raspberry pi for company Acme and Motherboard model X4282 for Stark industries. I would read the new data from somewhere and update date_last_checked to now() so it now have the newest date.

However, now and then a new part is introduced. So table 'Parts' would get a new row. This new part is not yet introduced in the table last_checked.

What I would like to have is a query that shows the entries for all companies and all parts. The date for company/part combinations that already exist in table last_checked are used verbatim and entries where a part exists that does not have a company should be in there with a date of now() - 100 days. That way, new parts will alway be first in the list. After they are read, the new company/part combination will be inserted in last_checked and start following the usual cycle.

I can create 2 queries that combined have the correct info; this gets all parts for Acme that do not yet exist in table last_checked

SELECT parts.id, now() - INTERVAL '100 DAYS' AS date 
FROM parts p 
WHERE p.id NOT IN (SELECT l.part_id_fk FROM last_checked l WHERE l.company_id_fk = 1)

And this selects all existing parts for Acme:

SELECT l.part_id_fk FROM last_checked l
WHERE l.company_id_fk = 1

These combined 2 queries is what I need but more generic so I can SELECT a company from all existing companies. I am however unclear how to combine these. I have tried a UNION query, but because the dates are different, there is no match and more records than needed are selected. I also do not know how to do the check for company id in both queries.

I have tried JOIN, LEFT JOIN, CROSS JOIN but everything I do does not seem to get the right result. The main reason I can't get it to work is because I have to combine the 2 queries and then SELECT the company_id, so something like:

SELECT * FROM
(Query1 combined with Query2) WHERE company_id = 1.

Is there a solution to this?

Answer 1

SQL does allow/provide the following:

SELECT
  l.id,
  cp.company_id,
  cp.part_id,
  coalesce(l.date_last_checked, cast(now() - INTERVAL '100 DAYS' as timestamp(0))) AS date_last_checked
FROM last_checked l
right join (
  select c.id as company_id, p.id as part_id
  from Companies c
  cross join Parts p
  ) cp
  on l.company_id_fk = cp.company_id
  and l.part_id_fk = cp.part_id
WHERE cp.company_id = 1
;

However, runtime might become a problem if Companies and Parts grow. At this time, you might want to switch to a UNION ALL of the two queries, which each provide part of the result, you need.

See it in action: SQL Fiddle

Please comment, if and as this requires adjustment / further detail.