Return mixed results from parent and child table with condition on child table (MSSQL)

All we need is an easy explanation of the problem, so here it is.

The parent table is storing the latest update time for machine2 and machine3, but the updates themselves are stored in the child table, and I would like to return the latest update time for each machine that is a check (latest "check" record of the machine in the child table by time).

I can join the two, but I don’t know how to state the condition, because I want to return other data which is in the parent table (e.g. tester)

Parent table

id (PK) time title tester
2 23:50 machine2 tester X
3 18:40 machine3 tester Y

Child table

id time notes Parent_entry (FK)
1 23:50 test 2
2 20:50 check 2
3 19:20 check 2
4 18:40 test 3
5 14:30 check 3

Result needed

time title tester
20:50 machine2 tester X
14:30 machine3 tester Y

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can use a window function such as ROW_NUMBER() inside of a CTE to generate a unique integer series partitioned by each Parent_entry and ordered by the time descending and then use that dataset to filter out the latest records where the notes = 'check' like so:

WITH CTE_ChildTable_CheckOnly_Sorted AS
(
    SELECT 
        [time], 
        Parent_entry, 
        ROW_NUMBER() OVER (PARTITION BY Parent_entry ORDER BY [time] DESC) AS SortId
    FROM ChildTable
    WHERE notes = 'check' -- Only "check" rows
)

SELECT S.[time], PT.title, PT.tester
FROM CTE_ChildTable_CheckOnly_Sorted AS S
INNER JOIN ParentTable AS PT
    ON S.Parent_entry = PT.Parent_entry
WHERE S.SortId = 1 -- Filters out only the latest rows by time

Note if your ParentTable has rows that don’t exist in the ChildTable, and you want to keep those rows in the final results, then you need to use an outer join instead of an inner, and will need to do an ISNULL() on S.[time] and replace with PT.[time]. If all ParentTable rows have at least one correlating ChildTable row, then the above example is fine.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply