CODEWITHSUNDEEP
phpmysqlsql
BaconJuice
BaconJuice

Reputation: 3769

selecting a set of rows with specific record data

I here today with a question that has left me rather confused and puzzled. Perhaps someone out there could give me a bit of assistance.

I have three table as followed. Please note that they are simplified for this question.

//table_checks//

check_id |task_id| status
   1     |  1    | Done
   2     |  1    | Done
   3     |  1    | Done
   4     |  2    | Done
   5     |  2    | Not Done
   6     |  2    | Not Done
   7     |  2    | Not Done
   8     |  2    | Done
   9     |  3    | Done
  10     |  3    | Done
  11     |  3    | Not Done
  12     |  3    | Done

//table_user//

user_id |  email   | type
   1    |  [email protected] | IN
   2    |  [email protected] | IN
   3    |  [email protected] | EX

//table_chk_usr//

check_id |user_id 
   1     |  1    
   2     |  1    
   3     |  1    
   4     |  2    
   5     |  2     
   6     |  2     
   7     |  2     
   8     |  2     
   9     |  3     
  10     |  3     
  11     |  3     
  12     |  3

Here are three tables with its relation in the table_chk_usr table.

My question is how do I query and select rows of table_user with type 'IN' from table table_user where all of users assigned task_ids in the table_checks are with the status = done.

So the expected result should be as follows

//table_user//

user_id |  email   | type
   1    |  [email protected] | IN

Since user_id with 1 has completed all task_ids with status done.

I hope this makes sense to those of you who are reading. Any help will be much appreciated.

Please Note that I am using PHP as my server side language, if that helps in any way.

Upvotes: 0

Views: 60

Answers (2)

Sturm
Sturm

Reputation: 4285

You could use a join here, but there may be a better way to do it.

SELECT * FROM table_checks
INNER JOIN table_chk_usr
  ON table_checks.check_id = table_chk_usr.check_id 
INNER JOIN table_user
  ON table_chk_usr.user_id = table_user.user_id
WHERE table_checks.status = 'Done'
AND table_user.type = 'IN'

Upvotes: 0

Brian Hoover
Brian Hoover

Reputation: 7991

I think this is what you are looking for. 

select user.user_id, user.email, user.type, 
sum(if(status = 'done',1, 0)) as done, count(*) as checks
  from checks
   join chk_user on checks.check_id = chk_user.check_id
   join user on chk_user.user_id = user.user_id
   group by chk_user.user_id
   having done = checks

sqlFiddle

Upvotes: 2

Related Questions