I apologize if this question has been asked in a similar way before. I am rather new to SQL and am having a very difficult time finding the solution to my problem. Very confused.
I currently have three tables:
Templates - | id | name |
Users - | id | full_name |
Changelog - | id | id_user | id_template | last_edited_at
I have a table filled with templates.
I have a separate table full of users WHO make changes on those templates. Every time a change is made, an entry is created in the changelog table.
I am trying to create a query with the main goal being fetching EVERY record from templates, as well as WHEN it was most recently changed, and BY WHOM.
I came up with this query which allows me to find the most recent change for a specific template ID.
SELECT changelogs.id_user, changelogs.updated_at, users.full_name
FROM changelogs
JOIN users
ON changelogs.id_user = users.id
WHERE changelogs.id_template = :templateId
ORDER BY changelogs.updated_at DESC
LIMIT 1
I orignally tried to solve this problem by looping through each template record, and running the above query for each record ID, but that is slow and doesn't work. I know there must be a way to do this in SQL, and I wanted to see if anybody has solved a similar problem. I didn't design this database, so sorry if it is not optimized.
My ideal final result would be a table that looks like:
| template.id | template.name | user.name | changelog.updated_at |
Thanks so much in advance for any guidance
You can use two JOINs to get the information from all the tables and then use a SUBQUERY to get the maximum value for the updated_at field, like this:
SELECT
t.id as template_id, t.name as template_name,
u.name as user_name,
c.updated_at as changelog_updated_at
FROM
changelogs c
INNER JOIN templates t on c.id_template = t.id
INNER JOIN users u on c.id_user = u.id
WHERE
c.updated_at =
(SELECT MAX(updated_at) FROM changelogs c2 where c2.id_template = t.id)
ORDER BY t.template_name