SQL: Selecting Where Latest Sub Child

I have a few model开发者_JS百科 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

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.

I'm not where I can test this, but it sounds like you'll need a subquery to pull the max version numbers of each child, then a self-join to get the rest of the sub-child information. Something like this is what I'm thinking:

SELECT DISTINCT
  Parent_ID
FROM
  Parent JOIN Child
    ON Parent.Parent_ID = Child.Parent_ID
  JOIN (
    SELECT Child_ID, MAX(Version_Number)
    FROM Sub-Child
    GROUP BY Child_ID ) AS MaxSubchild
  JOIN Sub-Child
    ON Sub-Child.Child_ID = MaxSubchild.Child_ID AND
      Sub-Child.Version_Number = MaxSubchild.Version_Number
WHERE
  SUb-Child.Sent IS NULL AND
  Sub-Child.Void IS NOT NULL;

Start by getting the max version by child_id:

select child_id, max(version_number) as version_number
from subchild
group by child_id

Then join it as a subquery, with subchild and child, and apply your where condition.

Or, without subqueries, try,

SELECT DISTINCT p.parent_id
FROM sub_children sc
  LEFT JOIN children c ON sc.parent_id = c.child_id
  LEFT JOIN parents p ON c.parent_id = p.parent_id
WHERE sc.sent == null, but sc.void != null

You can also use Rank():

SELECT DISTINCT TOP 100 PERCENT ST.Parent_ID
FROM
(
    SELECT RANK() OVER (PARTITION BY C.Parent_ID ORDER BY SC.Version_Number DESC) AS [RANK], 
    C.Parent_ID, SC.Sent, SC.Void
    FROM Child C 
    INNER JOIN Sub_Child SC ON C.Child_ID = SC.Child_ID
) ST
WHERE [RANK] = 1
AND [Sent] IS NULL AND [Void] IS NOT NULL