SQL code to display counts() of value retrieved from another column
- by Doctor Trout
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.
