SQL code to display counts() of value retrieved from another column

Question

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.

Answer 1

try this:

Select * From Table3 t3
Where (Select Count(*) From Table1 t1r1
          Join Table2 t2r1 On t2r1.bookingid = t1r1.bookingid
       Where t2r1.projectid = t3.ProjectId
          And role = 'role1') <>  numberofrole1 
     Or
     (Select Count(*) From Table1 t1r2
          Join Table2 t2r2 On t2r2.bookingid = t1r2.bookingid
       Where t2r2.projectid = t3.ProjectId
          And role = 'role2') <>  numberofrole2

or even better, if it works... (try it)

Select * From Table3 t3
Where Exists 
      (Select * From Table1 t1r1
          Join Table2 t2r1 
               On t2r1.bookingid = t1r1.bookingid
       Where t2r1.projectid = t3.ProjectId
       Group By Role
       Having Sum(Case Role When 'role1' Then 1 Else 0 End) 
                              = t3.numberofrole1
          And Sum(Case Role When 'role2' Then 1 Else 0 End)  
                              = t3.numberofrole2 ) 

Answer 2

SELECT * FROM
(
  SELECT 
    projectid, 
    role,
    CASE WHEN role = 'role2' THEN numberofrole2 DEFAULT numberofrole1 END numRequired,
    COUNT(*) numPresent
  FROM Table1 
  JOIN Table2 ON Table1.bookingid = Table2.bookingid
  JOIN Table3 ON Table2.projectid = Table3.projectid
  GROUP BY projectid, role
)
WHERE numRequired > numPresent

Answer 3

Try something like that:

SELECT
    projectid, 
    numberofrole1,
    numberofrole2,
    SUM(role='role1') as actual_numberofrole1,
    SUM(role='role2') as actual_numberofrole2
FROM table3
JOIN table2
    USING (projectid)
JOIN table1
    USING (bookingid)
GROUP BY projectid
HAVING numberofrole1 <> actual_numberofrole1
    OR numberofrole2 <> actual_numberofrole2