Mysql Query With JOIN and Nested NOT IN

Question

So I have two tables. One is questions table and another one is the answers table with the below mentioned schema.

Question:

id title category_id active

Answer:

id question_id player_id active

Basically, what I need is a query to get the number of questions remaining grouped together for each player for a particular category_id

SELECT * FROM questions WHERE id NOT IN (SELECT DISTINCT(s.question_id) FROM answers s)

It returns me the remaining questions, but its not grouped together for particular player and if it is joined with the same answers table, then the result set is empty.

Query that I have tried:

SELECT g.player_id, COUNT(questions.question_id) as Count
FROM    `questions` JOIN answers g ON g.id = questions.id
WHERE g.question_id NOT IN
    (
    SELECT  DISTINCT(question_id)
    FROM  answers
    ) GROUP BY g.player_id

I have already got the desired result using PHP and used multiple loops to get the required set and it is not optimized solution and takes a lot of time. So, want to solve it by writing a proper query.

Thanks for your time and appreciate if anyone can guide me.

Table Data:

Questions:

id title category_id active
 1   A     1          1
 2   B     1          1
 3   C     1          1
 4   D     1          1
 5   E     1          1

Answers:

id question_id player_id active
 1    1           1       1
 1    3           1       1
 1    2           2       1
 1    4           3       1

Expected Output:

 player_id Count_of_Remaining_Questions Category ID
   1            3                           1
   2            4                           1
   3            4                           1

@Strawberry Oh! Forgot that. Thanks. Will keep in mind. Waiting for the query. — Kinshuk Lahiri
– Kinshuk Lahiri, Commented Jan 29, 2018 at 10:24
update your question add a proper data sample and the expected result — ScaisEdge
– ScaisEdge, Commented Jan 29, 2018 at 10:25
What if no-one had answered anything yet, where would you get your list of players from? — trincot
– trincot, Commented Jan 29, 2018 at 10:35

Paul · Accepted Answer · 2018-01-31 06:33:42Z

2

Let's assume a players table with this simplified schema:

id | name

Now you can join this table with your questions table:

SELECT 
  p.id AS player_id,
  q.id AS question_id
FROM
  players p
CROSS JOIN
  questions q

That would result in a list of all possible player-question combinations. Now, you could join that to the answers using a LEFT JOIN and filter out the answered ones:

SELECT 
  p.id AS player_id,
  q.id AS question_id
FROM
  players p
CROSS JOIN
  questions q
LEFT JOIN
  answers a 
    ON a.question_id = q.id 
    AND a.player_id = p.id
WHERE
  a.id IS NULL

This will reduce the former list to all unanswered questions. Now, just group that by player and category to get the final list:

SELECT 
  p.id AS Player,
  COUNT(q.id) AS Remaining,
  q.category_id AS Category
FROM
  players p
CROSS JOIN
  questions q
LEFT JOIN
  answers a 
    ON a.question_id = q.id 
    AND a.player_id = p.id
WHERE
  a.id IS NULL
GROUP BY
  p.id,
  q.category_id

Update: To get a list of players who already answered all questions we need to change the logic. Instead of checking for unanswered questions (WHERE a.id IS NULL) we will now compare the sum of question ids in both tables questions (to get the reference for all questions) and answers (to calculate the score for each player/category). Only if both values are equal (HAVING SUM(a.question_id) = SUM(q.id)), the individual player did answer all questions (assuming there is only one answer row per player and question.

SELECT 
  p.id AS Player,
  COUNT(q.id) AS Remaining,
  q.category_id AS Category
FROM
  players p
CROSS JOIN
  questions q
LEFT JOIN
  answers a 
    ON a.question_id = q.id 
    AND a.player_id = p.id
GROUP BY
  p.id,
  q.category_id
HAVING
  SUM(a.question_id) = SUM(q.id)

edited Jan 31, 2018 at 6:33

answered Jan 29, 2018 at 10:30

Paul

9,0523 gold badges30 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kinshuk Lahiri Over a year ago

Thanks, for your help. It works like a Charm. I did some little changes. However, I have one more thing to ask. It doesn't returns any player_id for any particular category when they have 0 question remaining. Can we get only those players count, whose questions remaining is 0?

Paul Over a year ago

Well, that's another task. By adding WHERE a.id IS NULL we specifically only list players that have at least one question remaining. Could you specify the requirements? That is, now you want a list of players that have answered all questions?

Kinshuk Lahiri Over a year ago

Yes, all the players who have answered all the questions.

Kinshuk Lahiri Over a year ago

Thanks! Doing the task with SQL shows huge improvements at my end.

trincot · Accepted Answer · 2018-01-29 10:53:38Z

1

You could cross join the answers and questions tables and then outer join the answers table again to find the missing answers:

select     a.player_id, 
           count(distinct q.id) as count
from       answers a
cross join questions q
left join  answers a2
        on a2.question_id = q.id
       and a2.player_id = a.player_id
where      a2.question_id is null
and        q.category_id = 1
and        q.active = 1
group by   a.player_id

answered Jan 29, 2018 at 10:53

trincot

357k38 gold badges282 silver badges339 bronze badges

Comments

ScaisEdge · Accepted Answer · 2018-01-29 12:25:25Z

1

You could use a cross join for get all the possible question for each user then with a not in the asnwer of each user you could obtain the value for your stats

   select player_id, count(*), category_id
   from (

      select a2.player_id, q.id, q.category_id 
      from answer  a2
      cross join question q 
      where (a2.player_id, q.question_id ) not in (
         select a.question_id, a.player_id
         from Answers a ) 
    ) t
    group by player_id, category_id

edited Jan 29, 2018 at 12:25

answered Jan 29, 2018 at 10:41

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Mysql Query With JOIN and Nested NOT IN

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related