Trying to select most recent date

[venerable bede]

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

[GaryMazzone]

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

[venerable bede]

Looks nice but can I ask:

Does the someKey refer to the foreign key in each of the child tables?

[GaryMazzone]

Yes... I assume that the PK of the Parent table is in each child table

[szlamany]

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

Do you want both rows - from tblChild1 and tblChild2 - but just the most recent from both?

Please be more clear.

[venerable bede]

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.

[szlamany]

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.

[venerable bede]

Its done.
I basically have the following :

select userID,  MAX(d1) as MaxCreationDate,  MAX(d2) as MaxUpdatedDate from
(
-- Select blah blah
) as temp
where temp.userID = @userId
group by temp.userID

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.