By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,610 Members | 1,613 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,610 IT Pros & Developers. It's quick & easy.

SQL Server Max Record, multiple table join problems

P: n/a
Thanks in advance for you help.
SQL Server 2000

I have a complex join, but a simple example will do the trick so...

table1 (placement records, possibly many per case,highest ID being
most recent)
----------
placementID(pk) * CaseID(fk) * OrganizationID(fk) * Name * Number

table2 (Organizations Table, Many Placements can be at 1 organization)
----------
OrganizationID(pk) * OrgName * OrgType

table 3(Case Table, each Case can have many placements)
----------
CaseID(pk) * StaffName * CreationDate

now my query...

SELECT t1.placementID,t1.caseID,t2.OrgName
FROM table1 as t1
INNER JOIN table2 as t2 on t2.OrganizationID = t1.OrganizationID
WHERE exists (select distinct max(placementID),CaseID
from t1 group by caseID)
GROUP BY t2.OrgName,t1.PlacementID,t1.CaseID

my results
-------------
placementID CaseID OrgName
1 1 oneOrg
2 1 two org
3 1 three org
4 2 another org
5 3 yet another org

my desired results
------------------
PlacementID CaseID OrgName
3 1 three org
4 2 another org
5 3 yet another org
as you can see i get all records, but what i want is to see only the
last placementID for each case so i dont want duplicate caseID but I
do need the orgName, and yes the query works correctly without the org
name but as soon as i add orgName to the select statement I get
duplicate CaseID's, How do i eliminate duplicate CaseID's and get only
the MAX(placementID) for each Case and the OrgName

please advise, getting desperate.
thanks again so much for the help
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 22 Apr 2004 17:09:24 -0700, Dave wrote:
Thanks in advance for you help.
SQL Server 2000

I have a complex join, but a simple example will do the trick so...

table1 (placement records, possibly many per case,highest ID being
most recent)
----------
placementID(pk) * CaseID(fk) * OrganizationID(fk) * Name * Number

table2 (Organizations Table, Many Placements can be at 1 organization)
----------
OrganizationID(pk) * OrgName * OrgType

table 3(Case Table, each Case can have many placements)
----------
CaseID(pk) * StaffName * CreationDate

now my query...

SELECT t1.placementID,t1.caseID,t2.OrgName
FROM table1 as t1
INNER JOIN table2 as t2 on t2.OrganizationID = t1.OrganizationID
WHERE exists (select distinct max(placementID),CaseID
from t1 group by caseID)
GROUP BY t2.OrgName,t1.PlacementID,t1.CaseID

my results
-------------
placementID CaseID OrgName
1 1 oneOrg
2 1 two org
3 1 three org
4 2 another org
5 3 yet another org

my desired results
------------------
PlacementID CaseID OrgName
3 1 three org
4 2 another org
5 3 yet another org
as you can see i get all records, but what i want is to see only the
last placementID for each case so i dont want duplicate caseID but I
do need the orgName, and yes the query works correctly without the org
name but as soon as i add orgName to the select statement I get
duplicate CaseID's, How do i eliminate duplicate CaseID's and get only
the MAX(placementID) for each Case and the OrgName

please advise, getting desperate.
thanks again so much for the help


Hi Dave,

Try this:

SELECT t1.placementID,t1.caseID,t2.OrgName
FROM table1 as t1
INNER JOIN table2 as t2 on t2.OrganizationID = t1.OrganizationID
WHERE t1.placementID = (select max(t1b.placementID)
from table1 as t1b
where t1b.caseID = t1.caseID)

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.