Printable View
SQL Server 2008
I have a parent table "tblParent" which has 2 child tables in a 1 to many relationship "tblChild1" and "tblChild2". Both child tables have a Date field "CreatedDate".
I need to perform a join between the parent table and the 2 child tables and I need to get the "CreatedDate" which is most recent.
I'm not sure how to do this as the 2 date fields reside in different tables.
Any Ideas guys/gals ??
Thanks In Advance :wave:
Try like this
Select someKey,MAX(CreateDate) From (
Select SomeKey Max(CreatedDate) From tblChild1 group by SomeKey
Union
Select SomeKey Max(CreatedDate) From tblChild2 group by SomeKey
)
Group By SomeKey
Looks nice but can I ask:
Does the someKey refer to the foreign key in each of the child tables?
Yes... I assume that the PK of the Parent table is in each child table
Do you want both rows - from tblChild1 and tblChild2 - but just the most recent from both?Quote:
Originally Posted by venerable bede
Please be more clear.
Damn.
I just realised that I also need to check a "deleted" field in each of the child tables for 0 and because its not part of the aggregate or group then I cant use it.Back to the drawing board.
Still sounds like just a sub-query with a where clause in the main where clause of a UNION'd select.
I'm still in need of clarification.
Its done.
I basically have the following :
What I need to do now is check to see which of the 2 dates is most recent and return that unless they are both null in which case return -1.:sick:Code:select userID, MAX(d1) as MaxCreationDate, MAX(d2) as MaxUpdatedDate from
(
-- Select blah blah
) as temp
where temp.userID = @userId
group by temp.userID