I am trying to implement a version control system (SOPs) where those documents are revised regularly and track whether users have read the most recent version of an SOP.
I can list all SOPs where the user has read the most current version, but I can't list all the SOPs that they are out of date on e.g. for notifications that they need to read them.
The SOP table may contain multiples e.g.
SOP Index SOP Code SOP Version
1 AAA 1
2 AAA 2
3 AAA 3
4 BBB 1
5 CCC 1
6 CCC 2
The SOP log looks like this:
SOP Index User ID SOP Version
1 1 1
2 1 2
4 1 1
So this shows user 1 has read the latest version of BBB, but has only read v2 of AAA although v3 is available.
I have managed to run a query that displays only the most recent SOP based on its code, e.g. from above 3, AAA, 3 | 4, BBB, 1 | 6, CCC, 2 but I am struggling to work out how to compare this to a separate table which logs the SOP index, user and version number and determine if they have read the most recent version or not.
This selects the most recent SOP:
SELECT a.sop_group, a.sop_code, a.sop_version, a.sop_title, a.sop_issued,
c.log_sop_version
FROM data_sops a
INNER JOIN (
SELECT sop_code, MAX(sop_version) sop_version, sop_index
FROM data_sops
GROUP BY sop_code) b ON a.sop_code = b.sop_code AND a.sop_version = b.sop_version
I've managed to extend this to select the SOPs that are the latest version the user has read with:
SELECT a.sop_group, a.sop_code, a.sop_version, a.sop_title, a.sop_issued, c.log_sop_version
FROM data_sops a
INNER JOIN (
SELECT sop_code, MAX(sop_version) sop_version, sop_index
FROM data_sops
GROUP BY sop_code) b ON a.sop_code = b.sop_code AND a.sop_version = b.sop_version
LEFT JOIN log_sops c ON a.sop_index = c.log_sop_sop_index
WHERE c.log_sop_user_index = '$userid' AND c.log_sop_version = a.sop_version
I thought the line WHERE c.log_sop_user_index = '$userid' AND c.log_sop_version = a.sop_version
could change operator from = to <> to give the opposite effect, but it doesn't seem to be that simple and 3 tables worth of joins in, I'm struggling to figure it out!