SQL Server Max Record, multiple table join problems

[Dave]

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

[Hugo Kornelis]

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)