Latest record on multiple tables

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

Other Forums

MS Access

Latest record on multiple tables

Author

Topic

sqlDan33
Starting Member

5 Posts

Posted - 2002-11-22 : 15:24:07

Structures goes as follow:

members(memberID, parentID, mySort, ...)
ranks(rankID, displayOrder, ...)
status(statusID, ...)
statusHistory(memberID, statusID, statusDate)
rankStatus(memberID, rankID, rankDate)

I need the following: memberID, rankID *latest*, displayOrder, statusID *latest* and mySort. I should also be able to specify a matching statusID and and parentID.

I'm using the following so far:

SELECT H3.memberID, H3.rankID, members.parentID, members.memberGameName, members.mysort
FROM [SELECT H1.memberID, H1.statusID, R1.rankID
FROM statusHistory AS H1, rankHistory AS R1
WHERE (((H1.statusID) In (SELECT TOP 1 statusID
FROM statusHistory AS H2
WHERE H2.memberID = H1.memberID
ORDER BY statusDate DESC
)))
AND
(((R1.rankID) In (SELECT TOP 1 rankID
FROM rankHistory AS R2
WHERE R2.memberID = R1.memberID
ORDER BY rankDate DESC
)))]. AS H3 INNER JOIN members ON H3.memberID=members.memberID
WHERE (((H3.statusID)=1) AND ((members.parentID)=0)

)
GROUP BY H3.memberID, H3.rankID, members.parentID, members.memberGameName, members.mysort
ORDER BY members.mysort DESC , members.memberGameName DESC;

However, I should be getting 4 records. But i'm getting 54 records (4 members x 18 ranks). I was wondering if anyone could lend a helping hand? Thanks.

<edit> moved to Access Forum </edit>

Edited by - robvolk on 11/22/2002 16:29:23

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-22 : 15:36:40

You need the latest date per member from each of these tables, and once you have that, you can then join them all together.



SELECT Member,
       SH.StatusID,
       RS.RankID,
       ..etc... [any other fields you need]
FROM
Members
INNER JOIN
(SELECT Member, Max(StatusDate) as MaxStatDate FROM StatusHistory) A
ON A.Member = Members.Member
INNER JOIN
(SELECT Member, Max(RankDate) as MaxRankDate FROM RankStatus) B
ON B.Member = Members.Member
INNER JOIN
statusHistory SH 
ON A.Member = SH.Member AND A.MaxStatDate = SH.StatusDate
INNER JOIN
rankStatus RS
on B.Member = RS.Member AND B.MaxRankDate = RS.RankDate

- Jeff

Edited by - jsmith8858 on 11/22/2002 15:37:46

sqlDan33
Starting Member

5 Posts

Posted - 2002-11-22 : 16:24:50

As soon as you use an aggregate function, don't you have to use GROUP BY?

Edited by - sqlDan33 on 11/22/2002 16:30:11

Edited by - sqlDan33 on 11/22/2002 16:34:00

sqlDan33
Starting Member

5 Posts

Posted - 2002-11-22 : 16:35:24

It works! Added the GROUP BY in the sub selects and the thing just went "tada!" :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-22 : 16:36:34

Ooops, forgot the group by's !! you're right:



SELECT Member,
       SH.StatusID,
       RS.RankID,
       ..etc... [any other fields you need]
FROM
Members
INNER JOIN
(SELECT Member, Max(StatusDate) as MaxStatDate FROM StatusHistory GROUP BY Member) A
ON A.Member = Members.Member
INNER JOIN
(SELECT Member, Max(RankDate) as MaxRankDate FROM RankStatus GROUP BY Member) B
ON B.Member = Members.Member
INNER JOIN
statusHistory SH 
ON A.Member = SH.Member AND A.MaxStatDate = SH.StatusDate
INNER JOIN
rankStatus RS
on B.Member = RS.Member AND B.MaxRankDate = RS.RankDate

give that a shot! Sorry!

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources