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.
|11||Motherboard model X4282|
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?