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



  • 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?



  • 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: http://sqlfiddle.com/#!17/fcd97/1

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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2