SQL: Selecting Where Latest Sub Child

Question

I have a few model which looks a little something like this: Parent has a 1-2-M relationship with Child, and Child has a 1-2-M relationship with Sub-Child.

Parent
------
Parent_ID


Child
-----
Child_ID,
Parent_ID


Sub-Child
---------
Child_ID,
Version_Number (numeric),
Sent (date),
Void (date)

I want a query which returns a list of unique parent_id's where the latest version (judged by the version_number) of a related sub-child is 'sent' == null, but 'void' != null.

I've been chewing this over in my head and can't figure things out.

Any advice would be greatly appreciated.

Thanks,

Robert

Answer 1

It'll be something like:

;WITH CTE_LatestSubChildren AS
(
    SELECT Parent_ID, Latest_Version_Number = max(sc.Version_Number)
    FROM
       Child c
       JOIN [Sub-Child] sc on c.Child_ID = sc.Child_ID
    GROUP BY c.Parent_ID

)
SELECT
    ParentID
FROM
    CTE_LatestSubChildren lsc
    JOIN Child c 
        on lsc.Parent_ID = c.Parent_ID
    JOIN [Sub-Child] sc 
        ON sc.Child_ID = c.Child_ID    
        AND sc.version_number = lsc.Latest_Version_Number   
        AND sc.Sent IS NULL    
        AND sc.Void IS NOT NULL

Note that this may require amendments as its not tested, and its not completely clear what should happen about multiple child records where the latest version is the same.