i am trying to build a view/query that hops across multiple joins showing one result row per left hand record, with the right hand record being the most recently-created record for that object in the table.

the end goal is to provide data to a front end application, but i need to be able to tell if there is no data in the right hand table while still displaying the data from the left hand table.

i don't know if i've worded that well; i'm a little out of my depth. maybe code will illustrate better than english. here are some tables to illustrate:

left hand table bill

id    description    extId
1     Some descr...  SB 123
2     Another de...  SB 124
3     Third desc...  SB 125

join table tally_bill

id    billId    tallyId
1     2         1
2     2         2
3     3         3
4     3         4

note that there is no entry for billId = 1

right hand table tally

id    countYes    countNo   created
1     4           0         2022-09-26 13:11:48
2     5           8         2022-09-26 14:50:24
3     10          11        2022-09-26 11:20:01
4     4           3         2022-09-26 13:41:25

my desired result looks something like this:

billId    description    extId    countYes    countNo
1         Some descr...  SB 123   null        null
2         Another de...  SB 124   5           8
3         Third desc...  SB 125   4           3

i need countYes and countNo to be the values from the most recently-created record in tally.

the query i've gotten so far is:

SELECT
    bill.id, bill.description, bill.extId,
    tally.id, tally.countYes, tally.countNo
FROM
    bill
LEFT JOIN tally_bill
    ON tally_bill.billId = bill.id
LEFT JOIN tally
    ON tally.id = tb.tallyId
    AND (
    SELECT
        MAX(tally.id)
    FROM
        tally_bill tb
    WHERE
        tb.billId = bill.id);

but this produces something to the effect of:

billId    description    extId    id    countYes    countNo
2         Another de...  SB 124   2     5           8
3         Third desc...  SB 125   4     4           3

as you see the record billId = 1 is missing. i have made a few variations on this query moving the subquery around, trying group bys and partitions, etc., to no avail. i'm at the limit of my SQL-fu, so i would appreciate any help or enlightenment, please :)

i'm using MySQL 8.0.30 but if there is a need to change versions i'm open to it.

CodePudding user response：

select    id
         ,description
         ,extId
         ,countYes
         ,countNo
from     (
         select   b.id
                 ,b.description
                 ,b.extId
                 ,t.countYes
                 ,t.countNo
                 ,row_number() over(partition by b.id order by t.id desc) as rn
         from     bill b left join tally_bill tb on tb.billId = b.id left join tally t on t.id = tb.tallyId
         ) t
where    rn = 1

Fiddle