I have three tables (these are the relevant columns):

Table1
bookingid, person, role


Table2
bookingid, projectid


Table3
projectid, project, numberofrole1, numberofrole2

Table1.role can take two values: "role1" or "role2".

What I want to do is to show which projects don't have the correct number of roles in Table1. The number of roles there there should be for each role is in Table3.

For example, if Table1 contains these three rows:

bookingid, person, role
7, Tim, role1
7, Bob, role1,
7, Charles, role2

and Table2

bookingid, projectid
7, 1

and Table3

projectid, project, numberofrole1, numberofrole2
1, Test1, 2, 2

I would like the results to show that there are not the correct number of role2s for project Test1.

To be honest, something like this is a bit beyond my ability, so I'm open to suggestions on the best way to do this. I'm using sqlite and php (it's only a small project). I suppose I could do something with the php at the end once I've got my results, but I wondered if there was a better way to do it with sqlite.

I started by doing something like this:

SELECT project, COUNT(numberofrole1) as "Role"
FROM Table1
JOIN Table2
USING (projectid)
JOIN Table3
USING (bookingid)
WHERE role="role1"
GROUP BY project

But I can't work out how to compare the value returned as "Role" with the value got from numberofrole1

Any help is gratefully received.