How to use MAX function with multiple records 1

[s121701]

This query suppose to show student last event completed information with some other information, this information is stored in multiple tables, problem with this query is I am getting multiple results for each student instead of showing their most recent information. Last function doesnt support on the system i m working on. Can any help!!

SELECT ACCOUNTS.FIRST_NAME,COURSES.COURSE,MAX(LSN_RESPONSES.DATE_SUBMITTED) AS LAST_EVENT_DATE,LSN_OBJECTS.TITLE AS EVENT_TITLE
FROM ACCOUNTS RIGHT JOIN
(LSN_RESPONSES LEFT JOIN
(COURSES RIGHT JOIN
(LSN_ENTRIES RIGHT JOIN LSN_OBJECTS ON LSN_ENTRIES.[OBJECT_ID]=LSN_OBJECTS.[OBJECT_ID])
ON COURSES.[COURSE_ID]=LSN_ENTRIES.[COURSE_ID])
ON LSN_RESPONSES.[PARENT_ID]=LSN_ENTRIES.[ENTRY_ID])
ON ACCOUNTS.[USERNAME]=LSN_RESPONSES.[USER_ID]
WHERE ((COURSES.COURSE_ID LIKE '______-_____-__') OR (ISNUMERIC(COURSES.COURSE_ID)>0))
AND (COURSES.[COURSE_ID]!='00225')
AND (ACCOUNTS.[USER_ID] LIKE '%.%.%')
AND (ACCOUNTS.USER_ID NOT LIKE '%.0999.%')
AND (ACCOUNTS.USER_ID NOT LIKE '%.0888.%')
GROUP BY ACCOUNTS.FIRST_NAME,LSN_OBJECTS.TITLE,COURSES.COURSE
HAVING DateDiff(d, MAX(LSN_RESPONSES.DATE_SUBMITTED),GetDate())>4
ORDER BY ACCOUNTS.FIRST_NAME

 

[PHV]

ON ACCOUNTS.[USERNAME]=LSN_RESPONSES.[USER_ID]
The above join seems suspect, I assume that USERNAME should be USER_ID.

You may try something like this:
SELECT A.FIRST_NAME,C.COURSE,L.LAST_EVENT_DATE,O.TITLE AS EVENT_TITLE
FROM ACCOUNTS A INNER JOIN (
SELECT R.USER_ID,MAX(R.DATE_SUBMITTED) AS LAST_EVENT_DATE
FROM LSN_RESPONSES R INNER JOIN LSN_ENTRIES E ON R.PARENT_ID=E.ENTRY_ID
WHERE R.USER_ID LIKE '%.%.%' AND R.USER_ID NOT LIKE '%.0999.%' AND R.USER_ID NOT LIKE '%.0888.%'
AND (E.COURSE_ID LIKE '______-_____-__' OR ISNUMERIC(E.COURSE_ID)>0) AND E.COURSE_ID!='00225'
GROUP BY R.USER_ID
HAVING DateDiff(d,MAX(R.DATE_SUBMITTED),GetDate())>4
) L ON A.USER_ID=L.USER_ID
INNER JOIN LSN_RESPONSES R ON L.USER_ID=R.USER_ID AND L.LAST_EVENT_DATE=R.DATE_SUBMITTED
INNER JOIN LSN_ENTRIES E ON R.PARENT_ID=E.ENTRY_ID
INNER JOIN COURSES C ON E.COURSE_ID=C.COURSE_ID
INNER JOIN LSN_OBJECTS O ON E.OBJECT_ID=O.OBJECT_ID
ORDER BY A.FIRST_NAME

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rac2]

Nested JOINs LEFT and RIGHT! Help!

SELECT
       ACCOUNTS.FIRST_NAME,
       COURSES.COURSE,
       MAX(LSN_RESPONSES.DATE_SUBMITTED) AS LAST_EVENT_DATE,
       LSN_OBJECTS.TITLE AS EVENT_TITLE

FROM ACCOUNTS
RIGHT JOIN LSN_RESPONSES ON
          LSN_RESPONSES.[USER_ID] = ACCOUNTS.[USERNAME]

RIGHT JOIN LSN_ENTRIES ON
          LSN_ENTRIES.[ENTRY_ID] = LSN_RESPONSES.[PARENT_ID]

LEFT JOIN COURSES ON
          COURSES.[COURSE_ID] = LSN_ENTRIES.[COURSE_ID]

RIGHT JOIN LSN_OBJECTS ON
           LSN_OBJECTS.[OBJECT_ID] = LSN_ENTRIES.[OBJECT_ID]

...

I hope this statement is equivalent to the one you posted. I prefer this layout because it shows me which column in the table is used to establish a relationship with another table. The other table is readily visible out there at the end of the line.

Could you put the relationships between entries, courses, responses and objects into a sentence? For example,

"An account is a student who may have responded with an entry into a course."

 

[s121701]

Hi PHV thanks for the post, I tried to use your query but i m not getting any result? , ACCOUNTS.[USERNAME]=LSN_RESPONSES.[USER_ID]. Using rac2 query i m geting mulitple records. any suggestion

 

[rac2]

Could you put the relationships between entries, courses, responses and objects into a sentence? For example,

"An account is a student who may have responded with an entry into a course."

 

[s121701]

Object:I need to find students who have not performed an activity in last 3 days, I need to list their name, last event(activity they completed), date they submitted last activity, name of activity.
"ACCOUNTS is table name that carries student account information, like student name, username"
ACCOUNTS.USERNAME=LSN_RESPONSE.USER_ID
"LSN_RESPONSES is a tables that carries student activity information, like date when student submitted an assignment(or event)"
LSN_RESPONSES.[PARENT_ID]=LSN_ENTRIES.ENTRY_ID[ENTRY_ID"LSN_ENTRIES is table that stores the entry information,each entry or activity submitted by student"
COURSES.[COURSE_ID]=LSN_ENTRIES.[COURSE_ID]
"COURSE TABLE STORES STUDENT COURSE INFORMATION, LIKE COURSE ID"
"LSN_OBJECTS STORES EACT ENTRY TITLE INFORMATION"
LSN_ENTRIES.[OBJECT_ID]=LSN_OBJECTS.[OBJECT_ID]

 

[PHV]

And this ?
SELECT A.FIRST_NAME,C.COURSE,L.LAST_EVENT_DATE,O.TITLE AS EVENT_TITLE
FROM ACCOUNTS A INNER JOIN (
SELECT R.USER_ID,MAX(R.DATE_SUBMITTED) AS LAST_EVENT_DATE
FROM LSN_RESPONSES R
INNER JOIN LSN_ENTRIES E ON R.PARENT_ID=E.ENTRY_ID
INNER JOIN ACCOUNTS U ON R.USER_ID=U.USERNAME
WHERE U.USER_ID LIKE '%.%.%' AND U.USER_ID NOT LIKE '%.0999.%' AND U.USER_ID NOT LIKE '%.0888.%'
AND (E.COURSE_ID LIKE '______-_____-__' OR ISNUMERIC(E.COURSE_ID)>0) AND E.COURSE_ID!='00225'
GROUP BY R.USER_ID
HAVING DateDiff(d,MAX(R.DATE_SUBMITTED),GetDate())>4
) L ON A.USERNAME=L.USER_ID
INNER JOIN LSN_RESPONSES R ON L.USER_ID=R.USER_ID AND L.LAST_EVENT_DATE=R.DATE_SUBMITTED
INNER JOIN LSN_ENTRIES E ON R.PARENT_ID=E.ENTRY_ID
INNER JOIN COURSES C ON E.COURSE_ID=C.COURSE_ID
INNER JOIN LSN_OBJECTS O ON E.OBJECT_ID=O.OBJECT_ID
ORDER BY A.FIRST_NAME

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[s121701]

Thanks for the post; this query is working except its showing me multiple course records for each student, is it possible to see student most recent activity in the most recent course.Thanks again I really appreciate your help.

 

[PHV]

multiple course records for each student
With different dates ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[s121701]

thanks alot its working fine. I was seeing students with same first name, I included their last name, and report seem to be working fine.THANKS ALOT!!!