How to join 2 tables and fill non matching entries with a date
Bogopo last edited by
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.
id name 1 ACME 2 Stark industries
id name 10 Raspberry PI 11 Motherboard model X4282
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?
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 ALLof the two queries, which each provide part of the result, you need.
See it in action: http://sqlfiddle.com/#!17/fcd97/1
Please comment, if and as this requires adjustment / further detail.