Select from SQL database information with newest revisions

Question

I coding web app for my client and have issue with selecting from database raports with newest revisions.

SELECT 
        raports.*,
        r1.*,
        users.*, 
        (SELECT COUNT(*) FROM changes WHERE changes.changes_raports_id = raports.raports_id) as changes,
        (SELECT changes.changes_date FROM changes WHERE changes.changes_raports_id = raports.raports_id ORDER BY changes.changes_date DESC LIMIT 1) as last_change,
        (SUM(injuries.injuries_min_procent) / COUNT(injuries_to_raports.injuries_to_raports_id)) as min,
        (SUM(injuries.injuries_max_procent) / COUNT(injuries_to_raports.injuries_to_raports_id)) as max
    FROM raports
    LEFT JOIN users 
        ON users.users_id = raports.raports_users_id 
    LEFT JOIN changes 
        ON changes.changes_raports_id = raports.raports_id 
    LEFT JOIN raports_to_changes r1
        ON r1.raports_to_changes_raports_id = raports.raports_id
    LEFT JOIN injuries_to_raports 
        ON injuries_to_raports.injuries_to_raports_raports_id = r1.raports_to_changes_raports_id
    LEFT JOIN injuries
        ON injuries_to_raports.injuries_to_raports_injuries_id = injuries.injuries_id
    WHERE r1.raports_to_changes_changes_id = (SELECT max(raports_to_changes_changes_id) FROM raports_to_changes r2 WHERE r2.raports_to_changes_raports_id = r1.raports_to_changes_raports_id)
    GROUP BY raports.raports_id ORDER BY raports.raports_id ASC;

In columns max and min i have not correct average from injuries. When i checked it and count all injuries i had 36 when true number is 2 but i have 18 revisions. So is logic that i have looped COUNT with all revisions but i want only the newest

I try changing WHERE statements and more LEFT JOINs but nothing helped.

Could someone fixed that code?

Thank you in advanced

Answer 1

Based on the clues revealed by your queries, the data model may look like this:

The select list shows that you need:

1. users information of a reports_id
2. aggregated injuries_min_procent and injuries_max_procent at raports_id level. (see cte_raport_injuries)
3. number of changes of a raports_id (see cte_raport_changes)
4. the last change_date of a raports_id (see cte_raport_changes)

I'm not sure about the need for raports_of_changes based on information revealed in the question, so I'm going to ignore it for now.

with cte_raport_injuries as (
select r.raports_id,
       sum(i.injuries_min_procent) / count(*) as injuries_min_procent,
       sum(i.injuries_max_procent) / count(*) as injuries_max_procent
  from raports r
  join injuries_to_raports ir
    on r.raports_id = ir.injuries_to_raports_raports_id
  join injuries i
    on ir.injuries_to_raports_injuries_id = i.injuries_id
 group by r.raports_id),
 cte_raport_changes as (
 select r.raports_id,
       count(c.changes_id) as changes, 
       max(c.changes_date) as last_change
  from raports r
  join changes c
   on r.raports_id = c.changes_raports_id
 group by r.raports_id)
select u.users_id,
       r.raports_id,
       ri.injuries_min_procent,
       ri.injuries_max_procent,
       rc.changes,
       rc.last_change
  from raports r
  join users u
    on r.raports_users_id = u.users_id
  join cte_raport_injuries ri
    on r.raports_id = ri.raports_id
  join cte_raport_changes rc
    on r.raports_id = rc.raports_id;

The result looks like this:

users_id|raports_id|injuries_min_procent|injuries_max_procent|changes|last_change|
--------+----------+--------------------+--------------------+-------+-----------+
       1|        11|             15.0000|             25.0000|      2| 2022-12-02|

So my question for you is what's in reports_to_changes that you need and what's its relationship between others? For further involvement from the community, you may want to share the following information in text format:

• DDLs of each tables (primary key, foreign key, column names & data types)
• Some representable sample data and basic business rules
• Expected output