Query to show all clients from a table with last event another 1

[brokengod]

To the brainstrust,

I have seen some similar questions as this, however due to being a bear of little brain, have had trouble adapting those solutions to my tables.

Basically, I have 2 tables with fields shown below:

caseTable
- caseID
- caseLodged
- caseAllocated etc

caseRequestTable
- caseLink
- caseRequestDate
- caseRequestType

The table are linked by caseID and caseLink.

I need the query to show all cases (from caseTable) with only the most recent request (from caseRequestTable). To date I have only managed to get results showing duplicates of the case with each request or nothing...

Appreciate any advice

bg

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[brokengod]

Wow, speedy response.

This is the basic query which shows all the duplicate cases with each request. I tried using top, but that just gives me the most recent case only, not each case record showing the most recent request:

SELECT DISTINCTROW caseRequestTable.caseRequestResubmit, caseRequestTable.casRequestOutcome, caseRequestTable.caseRequestType, caseTable.*
FROM caseTable LEFT JOIN caseRequestTable ON caseTable.caseID = caseRequestTable.caseLink
ORDER BY caseRequestTable.caseRequestResubmit DESC;

I know it needs work, but not sure how to tweak it.

 

[PHV]

Where is caseRequestDate ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[brokengod]

Sorry, my mistake, the caseRequestDate is actually called caseRequestResubmit. I amended the field date in the first request in a foolish attempt to make the questions simpler.

 

[PHV]

What about this ?
SELECT R.caseRequestResubmit, R.casRequestOutcome, R.caseRequestType, T.*
FROM (caseTable AS T
LEFT JOIN caseRequestTable AS R ON T.caseID = R.caseLink)
LEFT JOIN (
SELECT caseLink, Max(caseRequestResubmit) AS caseRequestDate
FROM caseRequestTable GROUP BY caseLink
) AS L ON R.caseLink = L.caseLink AND R.caseRequestResubmit = L.caseRequestDate
ORDER BY R.caseRequestResubmit DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[brokengod]

Hmmn, still getting several rows for each case with the request details the only difference...

 

[brokengod]

Strange,

The subquery:

SELECT caseLink, Max(caseRequestResubmit) AS caseRequestDate FROM caseRequestTable GROUP BY caseLink

returns one record for each case as is desired, but the overall query gives all the requests, not the Max...

 

[PHV]

What happens if you replace LEFT JOIN with INNER JOIN ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[brokengod]

Hahaha, looks good!

I think we are on the way. PHV, all those things they said about you were totally false.

Seriously, my thanks for the help and I will try to learn from this for next time.

God I love this site...