{"id":58,"date":"2007-07-20T11:40:04","date_gmt":"2007-07-20T17:40:04","guid":{"rendered":"http:\/\/thenoyes.com\/littlenoise\/?p=58"},"modified":"2007-07-23T08:00:28","modified_gmt":"2007-07-23T14:00:28","slug":"complete-set","status":"publish","type":"post","link":"https:\/\/thenoyes.com\/littlenoise\/?p=58","title":{"rendered":"Complete Set"},"content":{"rendered":"

Occasionally in #mysql the question pops up, “How can I get all the values from a table that satisfy all of multiple conditions?”<\/p>\n

Then we beat the questioner with a large stick, until they give an example of what they really mean.<\/p>\n

\r\nSELECT * FROM quizAnswers;\r\n+-------------+----------+\r\n| studentName | question |\r\n+-------------+----------+\r\n| seekwill    | A        |\r\n| seekwill    | B        |\r\n| seekwill    | C        |\r\n| roxlu       | A        |\r\n| fury        | B        |\r\n| fury        | B        |\r\n+-------------+----------+<\/pre>\n
Find all the students who have answered both questions ‘A’ and ‘B’.<\/em>
\n<\/p>\n
First shot at it is to group them together, and count their answers:<\/p>\n
SELECT studentName FROM quizAnswers GROUP BY studentName HAVING COUNT(*) = 2;<\/pre>\n
Sometimes that works, but it won’t in this case: fury has 2 answers, but to the same question. seekwill has 3 answers. We’ll get the wrong result.<\/p>\n
UPDATE: As Arlen<\/a> notes, this query works just fine if we change COUNT(*) to COUNT(DISTINCT question).  Now back to the regularly scheduled exercise in obfuscation.<\/em><\/p>\n
Next we turn to subqueries:<\/p>\n
SELECT DISTINCT studentName \r\nFROM quizAnswers \r\nWHERE \r\n    studentName IN (\r\n        SELECT studentName FROM quizAnswers WHERE question = 'A'\r\n    ) \r\n    AND studentNAme IN (\r\n        SELECT studentName FROM quizAnswers WHERE question = 'B'\r\n);\r\n+-------------+\r\n| studentName |\r\n+-------------+\r\n| seekwill    |\r\n+-------------+<\/pre>\n
Gets the right answer. But it doesn’t work in 4.0 or earlier (and people on Freenode always seem to use a shared host running 3.23). So we’ll rewrite it to use JOIN instead.<\/p>\n
SELECT DISTINCT studentName \r\nFROM \r\n    quizAnswers AS a \r\n    JOIN quizAnswers AS b USING (studentName) \r\nWHERE \r\n    a.question = 'A' \r\n    AND b.question = 'B';\r\n+-------------+\r\n| studentName |\r\n+-------------+\r\n| seekwill    |\r\n+-------------+<\/pre>\n
“But wait!” they say. “This example only had 2 values. My real data has 22 possible values. I don’t want to self-join the table 22 times (or write 22 subqueries). And the real values are random strings, not nice and sequential, so don’t try building the query in a loop or anything!”<\/p>\n
Never fear! Obscure and infrequently used string and aggregate functions to the rescue!<\/p>\n
SELECT studentName \r\nFROM quizAnswers \r\nGROUP BY studentName \r\nHAVING BIT_OR(1 << FIELD(question, 'A', 'B') - 1) = 3;\r\n+-------------+\r\n| studentName |\r\n+-------------+\r\n| seekwill    |\r\n+-------------+<\/pre>\n
Handling the rest of those 22 values is a simple matter of adding them to the parameters to FIELD(), and changing that 3 to (2^number_of_values - 1). We'll even put the comma-separated values into a user variable and let MySQL figure out the rest (note the switch to FIND_IN_SET here, since now we're using a single string of values):<\/p>\n
SET @q = 'A,B';\r\n\r\nSELECT studentName \r\nFROM quizAnswers \r\nGROUP BY studentName \r\nHAVING \r\n        BIT_OR(1 << FIND_IN_SET(question, @q) - 1) \r\n    = \r\n        (1 << LENGTH(@q) - LENGTH(REPLACE(@q, ',', '')) + 1) - 1; -- This is 2^numValues - 1\r\n+-------------+\r\n| studentName |\r\n+-------------+\r\n| seekwill    |\r\n+-------------+<\/pre>\n
How does it work? I'll let you look up the functions on your own, but here are the values each generates:<\/p>\n
SELECT \r\n    studentName, \r\n    question, \r\n    FIND_IN_SET(question, 'A,B,C') AS position, \r\n    1 << FIND_IN_SET(question, 'A,B,C') - 1 AS bitmap \r\nFROM quizAnswers;\r\n+-------------+----------+----------+--------+\r\n| studentName | question | position | bitmap |\r\n+-------------+----------+----------+--------+\r\n| seekwill    | A        |        1 |      1 |\r\n| seekwill    | B        |        2 |      2 |\r\n| seekwill    | C        |        3 |      4 |\r\n| roxlu       | A        |        1 |      1 |\r\n| fury        | B        |        2 |      2 |\r\n| fury        | B        |        2 |      2 |\r\n+-------------+----------+----------+--------+<\/pre>\n
SELECT \r\n    studentName, \r\n    BIT_OR(1 << FIND_IN_SET(question, 'A,B,C') - 1) AS groupBitMap \r\nFROM quizAnswers \r\nGROUP BY studentName;\r\n+-------------+-------------+\r\n| studentName | groupBitMap |\r\n+-------------+-------------+\r\n| fury        |           2 |\r\n| roxlu       |           1 |\r\n| seekwill    |           7 |\r\n+-------------+-------------+<\/pre>\n","protected":false},"excerpt":{"rendered":"
Occasionally in #mysql the question pops up, “How can I get all the values from a table that satisfy all of multiple conditions?” Then we beat the questioner with a large stick, until they give an example of what they really mean. SELECT * FROM quizAnswers; +————-+———-+ | studentName | question | +————-+———-+ | seekwill […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[4],"tags":[],"class_list":["post-58","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-W","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/58","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=58"}],"version-history":[{"count":0,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/58\/revisions"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=58"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=58"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=58"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}