MySQL query to set all members of a group to a status when one member performs an action

I'm adding a new feature to a system that is used to approve artwork files. One table lists all the pertinent details of a job version. Another table lists all the users who need to approve that version. When a new version is added, a list of users is chosen and they are all emailed an invite to come and approve the job.

New feature is that several users may belong to one particular group of users, so really only one of them needs to approve it.

If one user from the group approves it, I want to pull out all the other users who are marked and needing to approve that job and update their status to say they have approved it also.

The approvers table will have a list of several users for that job. I need to select all the users from that list that are in the same group as the one that is currently logged in and has made an approval.

As a start point I have added the logged in users' group to the session so I have something to start with.

I can pull out the list of users that are needed for this job no problem, what I then need to do is to check that list against the users table eliminating any that are in a different group or possibly are in the same group but have not been asked to make an approval.

Thought about trying to subselect but gave up.

Any pointers about a reasonably simple way to filter the results down further to just those users that are in this group and need to approve this job?

I've tried:

SELECT *开发者_开发百科 
FROM approvals 
RIGHT JOIN job_versions ON approvals.job_version_id = job_versions.ID
WHERE approvals.jobnumber = '5' 
AND approvals.job_version_ID = '42' 
AND approvals.groups LIKE '%legal%'  
AND approvals.approved ='N'

but that is also pulling in other users that aren't in 'legal'.

---

As far as I see, you need to make an update to your approvals table

UPDATE approvals set approved='Y' where approvals.jobnumber = '5' AND approvals.job_version_ID = '42' AND approvals.groups LIKE '%legal%' AND approvals.approved ='N'