Using INNER JOIN resulting table in another INNER JOIN

Question

I'm not really sure that the title actually corresponds, maybe my approach is wrong.

I have the following database structure:

TABLE producers
    id

TABLE data
    id
    date 
    value 
    producer_id OneToMany

First thing first, for each producer, I want to get the latest date of registered data that there is. The code below does exactly this:

SELECT producers.id AS producer_id, max.date AS max_date
FROM producers
INNER JOIN data ON producers.id = data.producer_id
INNER JOIN (
    SELECT producer_id, MAX(date) AS date
    FROM data
    GROUP BY producer_id
) AS max USING (producer_id, date)

And the resulting table is:

----------------------------------------------------
|   producer_id   |             max_date           |
----------------------------------------------------
|       5         |  2022-01-01 01:45:00.000 +0000 |
|       7         |  2022-01-01 01:45:00.000 +0000 |
|       14        |  2022-01-01 01:45:00.000 +0000 |
|       15        |  2022-01-01 01:45:00.000 +0000 |
|       17        |  2022-01-01 01:45:00.000 +0000 |
----------------------------------------------------

The next thing that I need is to SUM all the data records per producer WITH date bigger than the max_date we got for each producer after the INNER JOIN from the previous query. The SUM() will be performed on column value.

Hopefully that was clear, if not, let me know. I've tried doing another INNER JOIN and use table max in the WHERE clause but I got an error that told me that the table was there, but it wasn't possible to be used in that part of the query.

Maybe another INNER JOIN isn't the solution. Here I'm limited by my knowledge of SQL and I don't really know about which keywords to read more in-depth to understand what's the best approach and how to do it. So, an info to redirect me on the best path would be really helpful.

Thanks in advance.

EDIT: Forgot to specify on which column the SUM() will be executed on.

EDIT 2: Just realized that what I'm asking here, the result will always be an empty table because there won't ever be a record whose date will be bigger. When I wrote the simplified version of my database, forgot to add a table/join, that's why. But in the end imo the approach/solution will still be the same, just applied on a different table. Sorry for that again.

Your first query can be simple: sqlize.online/sql/psql14/44dd5bc0ba728fdd12a58b77c08c7fc4 — Slava Rozhnev
– Slava Rozhnev, Commented Jan 4, 2022 at 13:52
Good point, tho in my real scenario I do other INNER JOINs beside this one, so that's why I cannot do it this way. But I'll remember, thanks. — Cristian Gira
– Cristian Gira, Commented Jan 4, 2022 at 13:58
Please: Make the best question post you can, if you have an answer post that to. Don't unask your question & don't edit an answer into it. But don't edit in a way that invalidates posted answers. How to Ask How to Answer Help center Meta Stack Overflow Meta Stack Exchange For code questions give a minimal reproducible example. Please don't include greetings, apologies, etc. Please don't insert the word "EDIT" etc or otherwise say you edited, just edit to the best presentation possible. — philipxy
– philipxy, Commented Jan 4, 2022 at 17:05

Stefanov.sm · Accepted Answer · 2022-01-04 13:52:33Z

3

The first query in the question can be greatly simplified using distinct on and order by:

select distinct on (p.id)
       p.id, d.date
from producers p
join data d on p.id = d.producer_id
order by p.id, d.date desc;

As for "SUM all the data records per producer WITH date bigger than the max_date" - well, none exists with date bigger than the latest one. Here is a query to do so (even the result will be empty)

select producer_id, sum(value)
from data d inner join -- the query above follows
(
  select distinct on (p.id)
         p.id producer_id, d.date
  from producers p
  join data d on p.id = d.producer_id
  order by p.id, d.date desc
) t using (producer_id)
where d.date > t.date
group by producer_id;

edited Jan 4, 2022 at 13:52

answered Jan 4, 2022 at 13:28

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stefanov.sm Over a year ago

By the way the first query can be simpler then mine as @SlavaRozhnev suggests in his comment.

Collectives™ on Stack Overflow

Using INNER JOIN resulting table in another INNER JOIN

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related