0

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?

1 Answer 1

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.

1
  • Thank you, this is actually exactly what I need. I never thought of using coalesce in this query, it works like a charm! Sorry for the late response, I was ill so couldn't/wouldn't check stackexchange.
    – Tjeerd
    Mar 3, 2022 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.