CODEWITHSUNDEEP
sqljoin
Danny Drongo
Danny Drongo

Reputation: 33

Joining 3 Tables Using Newest Rows

I have 3 tables in my database: children, families, statuslog

Every time a child is checked in or out of the database, it is updated in the statuslog. I've done this a long time ago, but I can't seem to figure out how to do it anymore. I want to create a new view that joins all 3 tables, but I only want the newest entry from statuslog (by using the highest id).

For example, statuslog looks like this:

childID     researcher     status     id
1           Dr. A          out        1
1           Dr. A          in         2
1           Dr. B          out        3
1           Dr. B          in         4
2           Dr. C          out        5
2           Dr. C          in         6
3           Dr. B          out        7
3           Dr. B          in         8

This is what I want to do:

SELECT *
FROM children, families, statuslog
WHERE children.familyID = families.familyID AND children.childID = statuslog.childID

Obviously, this will return the children+families tuples coupled with every single log entry, but I can't remember how to only combine it with the newest log entry.

Any help would be appreciated!

Upvotes: 1

Views: 47

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

This seems to be the typical in which the higher id is interpreted as the newest. This query should do the trick:

select * from (
    select s1.* from statusLog s1
    left join statusLog s2
    on s1.childId = s2.childId and s1.id < s2.id
    where s2.id is null
) final
join children c on c.childId = final.childId
join families f on f.familyId = c.familyId

Correct any syntactical errors.

Upvotes: 0

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

Aggregate query with max(id) retrieves last ID given a childID. This is then joined to statuslog to retrieve other columns.

SELECT *
FROM children
INNER JOIN families
   ON children.familyID = families.familyID
INNER JOIN 
(
  SELECT childID, researcher, status
    FROM statuslog
    INNER JOIN 
    (
      SELECT childID, max(ID) ID
        FROM statuslog
       GROUP BY childID
    ) lastSL
    ON statuslog.childID = lastSL.childid
   AND statuslog.ID = lastSL.ID
) sl
  ON children.childID = sl.childID

Upvotes: 2

Related Questions