Geek asks and answers

SQL: choosing where the last child is located

I have several models that look a little something like this: the parent has a 1-2 M relationship with the Child, and the Child has a 1-2 M relationship with the Sub-Rib.

Parent
------
Parent_ID


Child
-----
Child_ID,
Parent_ID


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

I need a query that returns a list of unique parent_id, where is the latest version (judging by the version number) of the associated child 'sent' == null, but 'void' != null.

I chewed it in my head and I can not understand what is happening.

Any advice is appreciated.

Thank,

Robert

+3
source share
5 answers

It will 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

, , , , , .

+3

, , , , , . - - :

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;
+1

Start by getting the maximum version of child_id:

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

Then attach it as a subquery, with children and a child, and apply the where clause.

0
source

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
0
source

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
0
source

More articles:


All Articles