Basic subqurey question on getting max record with row detail

[vmauro]

I have the following query

-------
SELECT
e.encounterID,
e.patientID,
p.ControlNo
e.date AS Enc_date,
e.facilityId

FROM enc e

INNER JOIN patients p
ON e.patientID = p.pid
INNER JOIN users u
ON p.piD = u.uid

WHERE (((e.date) >= DATE_SUB(NOW(), INTERVAL 24 MONTH)) AND ((e.encType)=1) AND ((e.encLock)=1))
AND (e.facilityId IN (12,37,14,18,19,13,16,36))AND ((u.delFlag) =0)
--------

all I want to do is get the last Enc_date, with encounterID and patientID.

I know I need to use a subquery w/ a inner join.

I just dont understand how to put it together. Could someone please help me get the max date with related coulmns and help explain it to me. I reviewed this forum and was able to get a max query, but I dont know how to put it together.

thanks in advance

 

[r937]

you did not say so, but are you looking to find the row with the latest encounter date per patient?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[vmauro]

yes, but i want to show in each row

Max enc_date with

e.encounterID, e.patientID, p.ControlNo e.facilityId

 

[r937]

try this --

SELECT e.encounterID
     , e.patientID
     , p.ControlNo 
     , e.date        AS last_Enc_date
     , e.facilityId 
  FROM patients AS p
INNER 
  JOIN users AS u
    ON u.uid = p.piD
   AND u.delFlag = 0
INNER 
  JOIN ( SELECT patientID
              , MAX(date) AS last_date
          WHERE date >= CURRENT_DATE - INTERVAL 24 MONTH
            AND encType = 1 
            AND encLock = 1 
            AND facilityId IN (12,37,14,18,19,13,16,36)
           FROM enc 
         GROUP
             BY patientID ) AS m
    ON m.patientID = p.pid
INNER 
  JOIN enc e
    ON e.patientID = p.pid
   AND e.date = m.last_date
   AND e.encType = 1 
   AND e.encLock = 1 
   AND e.facilityId IN (12,37,14,18,19,13,16,36)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[vmauro]

Thanks, I had to play with it for awhile but I seem to get it to work. I needed to add a second where cluase to the end of the code. I am not sure why but I will follow it up during the week . Thanks again for your help. Now I just need to understand it more

this is what I ended up using (i still have to test some records.

------
SELECT
e.encounterID,
e.patientID,
p.ControlNo,
last_date,
e.encLock,
e.facilityId

FROM patients AS p

INNER JOIN users AS u ON u.uid = p.piD

INNER JOIN (SELECT patientID, MAX(enc.date) AS last_date
FROM enc
WHERE enc.facilityId IN (12,37,14,18,19,13,16,36)
AND ((enc.encType) = 1)
AND ((enc.encLock) = 1)
AND ((enc.date) >= DATE_SUB(NOW(), INTERVAL 12 MONTH))
GROUP BY patientID) AS m ON m.patientID = p.pid

INNER JOIN enc e ON e.patientID = p.pid
AND ((e.date) = m.last_date)
WHERE facilityId IN (12,37,14,18,19,13,16,36)
AND ((encType) = 1)
AND ((encLock) = 1)
AND ((last_date) >= DATE_SUB(NOW(), INTERVAL 12 MONTH))

ORDER BY 4 ASC

 

[r937]

may i ask why you put back those unnecessary and useless parentheses that i went to the trouble of removing?





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[r937]

just curious

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[vmauro]

I got the following error at the where clause

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE date >= CURRENT_DATE - INTERVAL 24 MONTH
AND encType = 1
' at line 14
"

I am new to these more complex queries and didnt now why it was causing a problem. So I started trying new things and thought that maybe it was a "(" problem. I am using a book called "sql for mere mortals" and some other books that have use of the "(".

 

[r937]

okay, thanks for posting the error message, i can see now what caused it, i had given you some dodgy SQL

i posted this --

INNER 
  JOIN ( SELECT patientID   
              , MAX(date) AS last_date  
          WHERE date >= CURRENT_DATE - INTERVAL 24 MONTH   
            AND encType = 1   
            AND encLock = 1     
            AND facilityId IN (12,37,14,18,19,13,16,36)  
           [red]FROM enc[/red]  
         GROUP  
             BY patientID ) AS m
    ON m.patientID = p.pid

see the FROM clause? it is misplaced, so naturally with the WHERE clause coming right after the SELECT clause, there was an error right on the WHERE keyword (exactly where the error message said it was)

the correct code is --

INNER 
  JOIN ( SELECT patientID   
              , MAX(date) AS last_date  
           [red]FROM enc[/red]  
          WHERE date >= CURRENT_DATE - INTERVAL 24 MONTH   
            AND encType = 1   
            AND encLock = 1     
            AND facilityId IN (12,37,14,18,19,13,16,36)  
         GROUP  
             BY patientID ) AS m
    ON m.patientID = p.pid

sorry for the confusion

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon