CODEWITHSUNDEEP
mysql
Kinshuk Lahiri
Kinshuk Lahiri

Reputation: 1500

Mysql Query With JOIN and Nested NOT IN

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

Upvotes: 0

Views: 86

Answers (3)

Paul
Paul

Reputation: 9022

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)

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133370

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

Upvotes: 1

trincot
trincot

Reputation: 350345

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

Upvotes: 1

Related Questions