5

I have a table results with this fields:

  • id
  • results_id
  • number_one (int)
  • number_two (int)
  • number_three (int)
  • number_four (int)
  • number_five (int)

Each number_* column can contain a number from 01 to 90.

I have a search form with 90 checkboxes. A user can tick up to 5 checkboxes.

Once an user select n checkbox, I need to build a query that look for a row that contains all those values but a number can be in any of the 5 columns.

Example:

an user ticks checkbox 02, 12 and 20. I need to retrieve all the rows with all three number but I a number can be in any of the number_* column. The 20 can be in column number_one, 02 could be in column number_three and 12 in column number_four. How can I build this query?

Also, would it be better to use one column instead of five columns? results table:

  • id
  • result_id
  • numbers (string)

column numbers contains 02-12-20-57-84 where - is the separator.

Example: an user ticks checkbox 02, 12 and 20. so my query would be

SELECT * 
FROM results 
WHERE numbers LIKE "%02%" 
    AND numbers LIKE "%12%" 
    AND numbers LIKE "%20%";
  • What database are you using? – Kennah Dec 30 '15 at 23:16
  • My database is MySQL – Tyler Durden May 11 '18 at 20:59
6

Personally I would create your table

  • id
  • result_id
  • value

But with a single value in the value column. Now you can index on the value column and that should dramatically speed things up. Your query would then look like this:

SELECT result_id
FROM TableName
WHERE VALUE IN ('02','12','20')
GROUP BY result_id
HAVING COUNT(1) >= 3

One of the benefits of this is that you can now easily check for 1 match, 2 matches etc. You can also increase the number of items they could check to 50 and your table structure wouldn't have to change.

That being said right now that won't work for you. Right now I would use something like this:

SELECT *
FROM TableName
WHERE '02' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '12' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '20' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  • 1
    I'm thinking the OP is designing something along the lines of Megabucks .. where a person would buy a ticket (the id) ... for a specific drawing (the result_id) .. containing 5 selected values (number_*) .. I like your answer, but if that's what they're doing, the (better) table you have proposed wouldn't quite fit his needs. Naturally, this is just a guess on my part, but you could easily add a bit to your answer to address it if you would like. :) Giving you a +1 regardless. – Joishi Bodio Dec 30 '15 at 23:15
  • 1
    @JoishiBodio The nice thing about the EVP (entity value pair) model is that it can match 5 matches, 10 matches, whatever with no change in schema and minimal in code. With different columns like the OP has now you would have to add columns each time you increase the number of values searched for. So in your MegaBucks example, what if they decide to add a 6th number? – Kenneth Fisher Dec 30 '15 at 23:27
  • 1
    I understand that completely. I was more pointing out (if that's an example of what he wants) that if the id were a ticket instance, you're short an id column... (assuming id was the PKEY at least). I'd need to have 1, 1, 15, 1, 1, 20, 1, 1, 22, 1, 1, 68, and 1, 1, 76 .. perhaps specify what you'd use as pkey in your table? – Joishi Bodio Dec 30 '15 at 23:30
  • 1
    (id + value would work, of course ... but I tend toward a preference for surrogate keys if it's not a join table, so ... shrug) – Joishi Bodio Dec 30 '15 at 23:37
  • 1
    @JoishiBodio I assumed result_id was a FKEY into another table with ID the PKEY. – Kenneth Fisher Dec 31 '15 at 1:02
0

This assumes id is a Primary Key, and assumes you can use CTEs in your RDBMS.

This will work regardless of the number of values in #Temp.

If(OBJECT_ID('tempdb..#Temp') Is Not Null)  Drop Table #Temp

CREATE TABLE #Temp
(
    selectedValue INT
)

INSERT INTO #Temp (selectedValue)
VALUES
    (2)
    , (12)
    , (20)

; WITH
    CTE1 AS
    (
        SELECT id, number_one AS number FROM results
        UNION SELECT id, number_two AS number FROM results
        UNION SELECT id, number_three AS number FROM results
        UNION SELECT id, number_four AS number FROM results
        UNION SELECT id, number_five AS number FROM results
    )
    , CTE2 AS
    (
        SELECT c1.id
        FROM CTE1 c1
            INNER JOIN #Temp t ON c1.number = t.selectedValue
        GROUP BY c1.id
        HAVING COUNT(*) >= (SELECT COUNT(*) AS numberOfSelectedValues FROM #Temp)
    )
SELECT r.*
FROM CTE2 c2
    INNER JOIN results r ON c2.id = r.id
  • Won't this return results if any of the values exist? Not if all of them exist at once? – Kenneth Fisher Dec 30 '15 at 23:09
  • If you put ; at the end of each statement, you won't have to put it before WITH. – Andriy M Dec 31 '15 at 12:27
  • @AndriyM, I think that's my OCD. I tend not to use semicolons, and CTEs are the one area where I do--which mostly comes from the examples I've seen online. I wonder if others OCD is feeding my OCD? – Kennah Dec 31 '15 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.