Search multiple values in multiple columns

Question

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 a 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 five columns.

Example:

a 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: a 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%";

UPDATE: I am using MySQL at the moment with this setup:

• id
• results_id
• number_one (int)
• number_two (int)
• number_three (int)
• number_four (int)
• number_five (int)
• date
• city (foreign key)

I am planning to move look for optimisation and faster alternatives. I can migrate to any other database just for this specific table. I can also change table structure if needed. There are around 150k records in that table doing a query similar to:

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)

Will a migration to another DBMS type bring benefits?

Answer 1

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)

Answer 2

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