Sorting query results by similarity/relevance of specific field

Question

this is my table , basically different way to store 2 football teams names (host and guest)

-----------------------------------------------------------------------------
|host|host_aio|h_unchar|h_a|h_b|guest|guest_aio|guest_unchar|guest_a|guest_b|
-----------------------------------------------------------------------------

i have a host and a guest name and i will break them down in different ways to find the corresponded rows to searched teams

for example in case of Melbourne Victory VS Brisbane Roar my query would be :

SELECT `host`,`guest`,`h_aio`,`g_aio`,`h_unchar`,`g_unchar`,`h_a`,`h_b`,`g_a`,`g_b` 
FROM `tbl` WHERE ( 

host  IN ('melbourne victory','melbournevictory','melbourne','victory')|| 
h_aio IN ('melbourne victory','melbournevictory','melbourne','victory')||
h_unchar IN ('melbourne victory','melbournevictory','melbourne','victory') ||
h_a IN ('melbourne victory','melbournevictory','melbourne','victory') ||
h_b IN ('melbourne victory','melbournevictory','melbourne','victory') ||
h_c IN ('melbourne victory','melbournevictory','melbourne','victory') ||
h_d IN ('melbourne victory','melbournevictory','melbourne','victory') ||
h_g IN ('melbourne victory','melbournevictory','melbourne','victory') )

AND ( 

guest IN ('brisbane roar','brisbaneroar','brisbane','roar')||
g_aio IN ('brisbane roar','brisbaneroar','brisbane','roar')||
g_unchar IN ('brisbane roar','brisbaneroar','brisbane','roar')||
g_a IN ('brisbane roar','brisbaneroar','brisbane','roar') ||
g_a IN ('brisbane roar','brisbaneroar','brisbane','roar') || 
g_b IN ('brisbane roar','brisbaneroar','brisbane','roar') ||
g_c IN ('brisbane roar','brisbaneroar','brisbane','roar') || 
g_d IN ('brisbane roar','brisbaneroar','brisbane','roar') ||
g_g IN ('brisbane roar','brisbaneroar','brisbane','roar') )

ORDER BY FIELD(`h_unchar`, 'melbournevictory'), FIELD(`g_unchar`, 'brisbaneroar')

here is the result :

i want to sort result by relevance/similarity of h_unchar and g_unchar with host and guest that's why i've added .

ORDER BY FIELD(`h_unchar`, 'melbournevictory'), FIELD(`g_unchar`, 'brisbaneroar')

but as you can see in the image the under 21 match (U21) is on the top how can i get the more similar row at the top ?

here is more info :

basically i get teams names from 2 different API the first api names are little different from the second one like ( fc barcelona and Barcelona )

and each api has a unique information which other api wont send ... so i need to somehow connect this 2 api and get all the info for each game from both .. i store the first one in database and when the second one send the info i search in database to find the first api info using teams names ( api 1 send its data before game and abpi 2 send data after game )

@RyanVincent basically i get teams names from 2 different API the first 1 names are little different from the second one like ( `fc barcelona` and `Barcelona` ) and each api has a unique information which other api wont send ... so i need to somehow connect this 2 api and get all the info fro each game from both .. i store the first one in database and when the second one send the info i search in database to find the first api info using teams names — max, Jan 15 '16 at 19:31
@RyanVincent actually this is the normalized table , this table only contains different variation of host and guest name and other data are stored in a different table ... and i cant normlize this table any more becuz i need to check host and guest name in a single query to avoid getting other teams with the similar names — max, Jan 15 '16 at 19:51

hretic · Accepted Answer · 2016-01-15T19:55:54.517

2

since you are searching for specific match/game and there are not many matches with the same host/guest names in each day i suggest you do this on the code side and avoid complicating already potential slow query

edited Jan 15 '16 at 19:55

answered Jan 15 '16 at 19:27

hretic

999
9
36
78

score 1 · Answer 2 · edited May 23 '17 at 11:44

This isn't a simple question, but something like this approach may help as a first pass, if you want something basic; How do I do a fuzzy match of company names in MYSQL with PHP for auto-complete?

In fact, this is a machine learning question, and you would need to build something like a clustering algorithm and find distances between the entries, or a predicting algorithm for what people are looking for; that's not a simple query question.

Sorting query results by similarity/relevance of specific field

2 Answers2