SQL code to display counts() of value retrieved from another column
I have three tables (these a开发者_运维问答re 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.
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 )
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
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
精彩评论