One-To-Many Joining Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

One-To-Many Joining Query

Author

Topic

adam2k1
Starting Member

6 Posts

Posted - 2009-03-16 : 07:30:12

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 08:09:06

Your query doesn't account for the latest start date. I don't think what you have is right.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 08:10:51

You can edit your query to this to account for startdate,

 SELECT tblReferral.ReferralID,
tblReferral.PersonID,
(SELECT TOP 1 Forename FROM tblNameHistory WHERE tblNameHistory.PersonID = tblReferral.PersonID order by startdt desc) Forename,
(SELECT TOP 1 Surname FROM tblNameHistory WHERE tblNameHistory.PersonID = tblReferral.PersonID order by startdt desc) Surname,
(SELECT TOP 1 AddrLine1 FROM tblAddressHistory WHERE tblAddressHistory.PersonID = tblReferral.PersonID order by startdt desc) AddrLine1,
(SELECT TOP 1 AddrLine2 FROM tblAddressHistory WHERE tblAddressHistory.PersonID = tblReferral.PersonID order by startdt desc) AddrLine2,
tblReferral.field1
FROM tblReferral

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 08:12:11

Or you can re-write it to this for better performance,

SELECT	a.ReferralID,
		a.PersonID,
		b.Forename,
		b.Surname,
		c.AddrLine1,
		c.AddrLine2,
		a.field1
FROM 
	tblReferral a
	join (select personid,max(dtStart) as MaxDt from tblNameHistory group by personid)b  on a.personId=b.personId and b.MaxDt=a.dtStart
	join (select personid,max(dtStart) as MaxDt from tblAddressHistory group by personid)c on a.personId=c.personId and c.MaxDt=a.dtStart

adam2k1
Starting Member

6 Posts

Posted - 2009-03-16 : 08:18:06

Sorry, that was a error in posting. my original query did include the ordering.

My problem though isn't with this query (or even your amended one, thanks) - I know that works and it returns the data I want.

The issue I have is that I suspect it's not very efficient and there is a better query i could use. Can anyone confirm if this is correct, and if so provide an alternative?

Thanks

adam2k1
Starting Member

6 Posts

Posted - 2009-03-16 : 08:20:46

Cheers,

Thats what I was looking for - a way to join on a one-to-many but only look at the latest record.

Adam

matty
Posting Yak Master

161 Posts

Posted - 2009-03-16 : 08:22:56

SELECT r.ReferralID,r.PersonID,nh.Forename,nh.Surname,ah.AddrLine1,ah.AddrLine2,r.field1
FROM tblReferral r
JOIN
(
SELECT PersonID,Forename,Surname,ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY dtStart DESC) AS Rownum FROM tblNameHistory
) nh
ON nh.PersonID = r.PersonID AND nh.rownum = 1
JOIN
(
SELECT PersonID,AddrLine1,AddrLine2,ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY dtStart DESC) AS Rownum FROM tblAddressHistory
) ah
ON ah.PersonID = r.PersonID AND ah.rownum = 1

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 08:23:00

Did you check the one posted @ 03/16/2009 : 08:12:11 ?

adam2k1
Starting Member

6 Posts

Posted - 2009-03-16 : 09:18:49

Thanks guys.

i don't have a large dataset to test the queries against but I will have soon enough - cheers for the advice.

Saket - started posting my response before I refreshed the page, so missed your 3rd post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources