JOIN question

Question

Post reply

JOIN question

Rajesh Hegde

Right there with Babe

Points: 761
More actions
November 10, 2005 at 2:57 pm

#166204

Hi,
I have a Header and a Details table, with a 1-N mapping from Header table to Details. The Details table contains a datetime column.
I want to join each row in the Header table to exactly one row in the Details table. For example - Header Row 1 matches 10 rows in the Details table. I want to join Header Row 1 to exactly one Details Row that has the greatest Date value.
How can I achieve this? Below is the sample data.
I would greatly appreciate your help.
Header Table(Mem_No)
Mem_No
1
2
3
Detail Table (Mem_No, CrDate)
Mem_No        CrDate
1                  2005-11-01
1                  2005-11-02
1                  2005-11-03
2                  2005-11-01
2                  2005-11-02
3                  2005-11-05

The JOIN table should read as
Mem_No        Mem_No      CrDate
1                   1              2005-11-03
2                   2              2005-11-02

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=235917

_____________
Code for TallyGenerator

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 2

SELECT Header.Mem_No, Detail.Mem_No, MAX(Detail.CrDate)

FROM Header INNER JOIN Detail ON Header.Mem_No = Detail.Mem_No

GROUP BY Header.Mem_No, Detail.Mem_No

Rajesh Hegde Right there with Babe Points: 761 More actions · Answer 3

Thanks for responding.

The group by query is useful. But, i need to futher filter the join table by the CrDate. From the join table I need to fetch only those rows where the CrDate > some_date.

I am basically looking at the join table where each header record is linked to the detail record with the max CrDate. This Join table will be further filtered based on other criteria. How can I do this?

Thanks,

Rajesh

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Why you don't open the link?

_____________
Code for TallyGenerator

Rajesh Hegde Right there with Babe Points: 761 More actions · Answer 5

Thank you Sergiy. I should i have read the link properly - it did answer my question.

Thanks.