mysql query for matching array of strings with database

Question

I have a simple database table which has the following structure:

Teams       Id_1           Id_2            Id_3 

team_1       A              B                C

team_2       A              B                D

team_3       E              F                D

team_4       X              null            null

team_5       D              A                B

now i have a sample array of string IDs the value of which is: A B. I want to select the team/teams based on the maximum match of this data point with the database, for example, the result of the match should give me team_1, team_2 and team_5 as output. What is an efficient way to do this query on mysql? Thanks in Advance.

thnks..position does not matter. team_5 will be returned too..just need to match regardless of the position. — kzs
– kzs, Commented Oct 3, 2013 at 9:31
What about [B, A] array? What if it will be [A, B, D, F] array? I.e. what is your 'maximum match'? — Alma Do
– Alma Do, Commented Oct 3, 2013 at 9:33
[B A] array is same as [A B] array, i want to select the row/rows which have maximum number of matching values with the array, for example, [A B D F] will return team_2 and team_5 — kzs
– kzs, Commented Oct 3, 2013 at 9:39

Kickstart · Accepted Answer · 2013-10-03 09:47:03Z

Assuming you want those with the best match (so if searching for [D Z] it would find the 3 items with D but as nothing contains Z that would be ignored, then maybe something like this (untested):-

SELECT *
FROM SomeTable
CROSS JOIN
(
    SELECT MAX(IF(FIND_IN_SET(ID_1, REPLACE("A B", " ", ",")) > 0, 1, 0) + IF(FIND_IN_SET(ID_2, REPLACE("A B", " ", ",")) > 0, 1, 0) + IF(FIND_IN_SET(ID_3, REPLACE("A B", " ", ",")) > 0 > 0, 1, 0)) AS MaxCount
    FROM SomeTable
    WHERE FIND_IN_SET(ID_1, REPLACE("A B", " ", ",")) > 0
    OR FIND_IN_SET(ID_2, REPLACE("A B", " ", ",")) > 0
    OR FIND_IN_SET(ID_3, REPLACE("A B", " ", ",")) > 0
) Sub1
WHERE IF(FIND_IN_SET(SomeTable.ID_1, REPLACE("A B", " ", ",")) > 0, 1, 0) + IF(FIND_IN_SET(SomeTable.ID_2, REPLACE("A B", " ", ",")) > 0, 1, 0) + IF(FIND_IN_SET(SomeTable.ID_3, REPLACE("A B", " ", ",")) > 0 > 0, 1, 0) = Sub1.MaxCount

Note if the list passed was comma separated you could avoid using all the replace statements.

asantaballa · Accepted Answer · 2013-10-03 09:36:01Z

1

Try this. Take the ID field and pad with space (the separator). Pad search string also with space. Then check if you can find the padded ID in the padded search string.

Select *
From Tbl
Where ' ' + ID_1 + ' ' in ' ' + SearchStr + ' '
   Or ' ' + ID_2 + ' ' in ' ' + SearchStr + ' '
   Or ' ' + ID_3 + ' ' in ' ' + SearchStr + ' '

answered Oct 3, 2013 at 9:36

asantaballa

4,0581 gold badge24 silver badges23 bronze badges

Collectives™ on Stack Overflow

mysql query for matching array of strings with database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related