CODEWITHSUNDEEP
mysqlsql
tibelchior
tibelchior

Reputation: 573

Mysql select match 4, 5 or 6 columns

I'm making a custom search to a DB that has 6 different columns. To make the search I have 6 different input fields that accept only numbers.

I already got the 6 columns search working, the code:

SELECT * FROM data 
WHERE 1_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a')
AND 2_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a')
AND 3_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a')
AND 4_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a')
AND 5_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a')
AND 6_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a')
ORDER BY id ASC;

This gives the correct result despite the order entered.

But I need the select to give me results whether it finds match to 4 or 5 of the numbers entered. I tried the OR but I don't know how to define the amount of columns matched.

Is this possible?

Thanks for the attention!

edit: -------------------

I got stuck with the code provided by dbaseman.

Because I get a true result but I really don't know whether it was generated by matching 4, 5 or 6 columns.

I looked to insert a CASE WHEN after, but isn't working.

Is there a way to know which combination of columns gave me the result?

Thought of something like (rough) if = 4, then result-4, else if = 5, then result-5...etc... And then be able to put the row in a array.

Thanks!

Upvotes: 2

Views: 181

Answers (1)

McGarnagle
McGarnagle

Reputation: 102783

Instead of using and/or, sum up the hits:

SELECT *,
    CASE WHEN 1_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_1,
    CASE WHEN 2_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_2,
    CASE WHEN 3_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_3,
    CASE WHEN 4_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_4,
    CASE WHEN 5_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_5,
    CASE WHEN 6_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_6,

    CASE WHEN 1_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 2_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 3_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 4_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 5_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 6_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    AS no_of_matches
FROM data 
WHERE 
    (
    CASE WHEN 1_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 2_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 3_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 4_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 5_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 6_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    ) in (4,5,6)
ORDER BY id ASC;

Alternate, a bit prettier using a nested query:

SELECT a.*, a.match_1+a.match_2+a.match_3+a.match_4+a.match_5+a.match_6 AS no_of_matches
FROM (
    SELECT *,
        CASE WHEN 1_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_1,
        CASE WHEN 2_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_2,
        CASE WHEN 3_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_3,
        CASE WHEN 4_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_4,
        CASE WHEN 5_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_5,
        CASE WHEN 6_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_6
        FROM data 
    ) a
WHERE a.match_1+a.match_2+a.match_3+a.match_4+a.match_5+a.match_6 in (4,5,6)
ORDER BY id ASC;

Upvotes: 4

Related Questions