The essence is as follows: There is a table

  id id_abitur quest answer 
 1 20170001 exam 9
 2 20170001 exam 10
 3 20170001 exam 17
 8 20170002 exam 9
 9 20170002 exam 10
 10 20170002 exam 14
 11 20170002 exam 17
 16 20170003 exam 9
 17 20170003 exam 10
 18 20170003 exam 14
 19 20170003 exam 17
 24 20170004 exam 9
 25 20170004 exam 10

id_abitur I need a sample id_abitur , in which quest = 'exam' and answer only 9,10,17 making a request

  SELECT `id_abitur`, COUNT (` id`) AS c 
 FROM meta_abitur 
 WHERE `quest` = 'exam' AND` answer` IN (9,10,17) 
 GROUP BY ʻid_abitur` 
 HAVING c = 3

They are displayed id 9,10,17 as well as in which 9,10,17,19, i.e. those that have another line. What am I doing wrong and how to fix it?

    4 answers 4

    Replace COUNT with SUM . If you answered the right questions, add one. If for others - we take away 1. We take only those where the sum is equal to three:

     SELECT `id_abitur`, sum(case when `answer` IN (9,10,17) then 1 else -1 end) AS c FROM meta_abitur WHERE `quest`='exam' GROUP BY `id_abitur` HAVING c = 3;

    http://sqlfiddle.com/#!9/ef21d2/5

      Option:

       SELECT DISTINCT `t1`.`id_abitur` FROM tab AS `t1` LEFT JOIN ( SELECT `t2`.`id_abitur` FROM tab AS `t2` WHERE `t2`.`answer` NOT IN (9,10,17) AND `t2`.`quest` = 'exam' ) AS `j` ON `t1`.`id_abitur` = `j`.`id_abitur` WHERE `j`.`id_abitur` IS NULL AND `t1`.`quest` = 'exam' GROUP BY `t1`.`id_abitur` HAVING COUNT(`t1`.`id_abitur`) = 3

      On SQL Fiddle .

        Group by field answer:

         SELECT id_abitur, COUNT(id) AS c FROM meta_abitur WHERE quest='exam' AND answer IN (9,10,17) GROUP BY answer HAVING c = 3
        • And what will we get here? Grouping by answer will give us answers, to which three students answered. - Zufir

        Another option: An internal subquery determines the number of responses for each applicant. An external query connects the meta_abitur table with a subquery based on the number (c) and numbers (answer in (9,10,17)) correct answers and the quest parameter
        select distinct id_abitur from meta_abitur i inner join (select id_abitur,count(*) as c from meta_abitur WHERE quest='exam' group by 1 having c=3) o USING(id_abitur) where answer in (9,10,17);

        • It will not filter out students who have given other answers - these answers will simply not be included in the sample and will not be processed in HAVING . - Zufir
        • @Zifur From the author's question "let's say I need a sample id_abitur, for which quest = 'exam' and answer only 9,10,17" others do not need answer - Alexus
        • So in the same place further: "Id are displayed 9,10,17 as well as for which 9,10,17,19, i.e. those who have another line. What am I doing wrong and how to fix it?". Your request will be the same. - Zufir
        • Here is the result of the query: 20170001 3 20170002 3 20170003 3 - Alexus
        • And there should be one line - 20170001. Only this ID gave ONLY the specified three answers. - Zufir