Selecting Latest Row From MAX Row of 3rd Table

debugcn Published at Dev

blankabout

This is a related query to this, but it involves 3 tables instead of 2.

We have 3 tables:


People:
personid, name

PeopleEvents:
personid,eventid,eventdate

EventTypes:
eventid,eventname,eventsequence

The tables would have data like:


People:
1,John
2,Mary
3,Jane
4,Rob

PeopleEvents:
1,100,1/1/2013
2,100,2/2/2013
2,102,2/3/2013
2,104,3/3/2013
3,100,4/4/2013
3,102,4/4/2013
4.100,2/2/2013
4,103,4/4/2013
4,104,3/3/2013

EventTypes:
100,Joined company,10
101,Induction,20
102,Introduction,17
103,Second meeting,25
104,First meeting,15

We want to be able to pull out a list of people based on highest EventType sequence, NOT event ID.

Thus the report would be:


John,Joined Company
Mary,Introduction
Jane,Introduction
Rob,Second meeting

This to me seems inelegant but appears to work:


SELECT p.personid,et.eventname FROM people p 
INNER JOIN peopleevents pe ON p.personid = pe.personid
INNER JOIN eventtypes et ON et.eventid = pe.eventid
WHERE p.personid NOT IN
(SELECT p1.personid FROM people p1 LEFT JOIN peopleevents pe1 ON p1.personid = p.personid
LEFT JOIN eventtypes et1 ON et1.eventid = pe1.eventid
WHERE p1.personid = p.personid AND et1.eventseq > et.eventset) ORDER BY p.name;

The way I see it the sub-query is attempting to find a higher sequence than the main query and thus only allowing the main query to return the highest sequenced event.

I will enter the answers provided to see if one of them will provide a tidier solution.

Devart

Try this query -

SELECT * FROM (
  SELECT p.*, et.eventname, et.eventsequence FROM people p
    JOIN PeopleEvents pe
      ON p.personid = pe.personid
    JOIN EventTypes et
      ON et.eventid = pe.eventid
  ORDER BY eventsequence DESC
) t
GROUP BY personid

Collected from the Internet

Please contact [email protected] to delete if infringement.