Reputation: 31
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
Upvotes: 3
Views: 135
Reputation: 149
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
Upvotes: 0
Reputation: 41569
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.
Upvotes: 3
Reputation: 25767
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
Upvotes: 0
Reputation: 78543
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.
Upvotes: 0
Reputation: 3826
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;
Upvotes: 1