0

Hello, I have a MSSQL database with a table called "people" and a table called "attendance". The people table contains the person's ID (primary key, identity increment), last name, first name, and active fields (I would only be interested in grabbing fields where active = Yes). The attendance table contains a primary key ID field (again, set as identity with auto increment), a person_id field (which is the same as the unique ID assigned to the person in the people table), and fields for date, time, and attendance (which is Yes or No).

Because of how the system is set up, the attendance table can have multiple rows for the same person for the same event. Let's say John Doe was assigned to attend an event on 7-1-12 at 9am. Then that same info was loaded into the table 3 more times. On the last load, however, the attendance field changed and was set to 'No'. Now there are 4 rows for John Doe, for the same date, same event, but I only need to grab the row that was most recently added, which in this case has his attendance set to No. I also need to grab his last name and first name from the people table so I can display it. I've tried everything I can think of, but can't get this to go.

3
Contributors
2
Replies
3
Views
5 Years
Discussion Span
Last Post by cutepinkbunnies
0
SELECT TOP 1 People.person_id, People.lastname, People.firstname, 
             Attendance.attendance_id, Attendance.date, Attendance.attendance
FROM People INNER JOIN
             Attendance ON People.person_id = Attendance.person_id
WHERE People.person_id = 1
ORDER BY Attendance.attendance_id DESC
0
SELECT A.ATTENDANCE_ID, P.PERSON_ID, P.FIRSTNAME, P.LASTNAME
FROM DBO.ATTENDANCE A
        INNER JOIN DBO.PEOPLE P
            ON P.PERSON_ID = A.PERSON_ID
WHERE P.FIRSTNAME = 'JOHN'
        AND P.LASTNAME = 'DOE'
        AND A.ATTENDANCE_ID = (SELECT MAX(ATTENDANCEID) FROM DBO.ATTENDANCE WHERE PERSON_ID = P.PERSON_ID)

Edited by cutepinkbunnies

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.