Complete set in SQL

Question

Hi,

I needed to come up with a SQL query that returns rows that satisfy multiple conditions. This article describes what I needed to do and the solution: http://thenoyes.com/littlenoise/?p=58

Basically it uses a bit operation to figure out if a provided string is found.. but I'm having hard time following how it works.

SET @q = 'A,B';

SELECT studentName
FROM quizAnswers
GROUP BY studentName
HAVING
        BIT_OR(1 << FIND_IN_SET(question, @q) - 1)
    =
        (1 << LENGTH(@q) - LENGTH(REPLACE(@q, ',', '')) + 1) - 1; -- This is 2^numValues - 1
+-------------+
| studentName |
+-------------+
| seekwill    |
+-------------+

I tested it and it works as expected. Can someone explain how this works?

Thanks, Amie

Answer 1

If there is a match for one of the values in the list, a bit is set and left-shifted as many times as the position of the matching item in the list. The group-by does an xor operation on all the individual bitmaps per student, resulting in a single value that contains the same information (one bit set for each matching value that existed).

This method is obviously limited by the number of bits that the return type can hold.

Answer 2

The MySQL documentation explains that FIND_IN_SET() "return(s) the index position of the first argument within the second argument". Given that the set of available questions was 'A', 'B', or 'C', we can use this query to see what FIND_IN_SET() is doing:

SELECT studentName, 
       question, 
       FIND_IN_SET(question, 'A,B,C') AS position 
FROM quizAnswers;

Using their example records, we'll get:

+-------------+----------+----------+
| studentName | question | position |
+-------------+----------+----------+
| seekwill    | A        |        1 |
| seekwill    | B        |        2 |
| seekwill    | C        |        3 |
| roxlu       | A        |        1 |
| fury        | B        |        2 |
| fury        | B        |        2 |
+-------------+----------+----------+

They're now suggesting you turn this into a bitmask and use bitwise xor to eliminate duplicates for any given (studentName, question) pair.

Answer 3

I am not going to explain what BIT_OR is doing.

Alternative solution:

If it is possible, I usually prefer pure SQL solutions that do not depend on the vendor-specific features. If what you need is similar to the example in the article you refer to, then this SQL statement should run pretty much on any RDBMS and produce the desired result:

-- //return those students that have correctly answered not less then 3 questions among A,B,C
select      studentName, COUNT(DISTINCT question) AS CNT
from        quizAnswers
where       question in ('A', 'B', 'C')
GROUP BY    studentName
HAVING COUNT(DISTINCT question) >= 3

and by playing with HAVING COUNT... you are even more flexible:

-- //return those students that have correctly answered at least 1 question among A,B (either A or B)
select      studentName, COUNT(DISTINCT question) AS CNT
from        quizAnswers
where       question in ('A', 'B',)
GROUP BY    studentName
HAVING COUNT(DISTINCT question) >= 1

Basically you just need to fill ... in part where question in (...) and set the value of the COUNT(...), which by default would be the number of answers to check for a Complete Set.