is there a way to do a search through a table:
SELECT a,b,c,d,e,f,g FROM table WHERE a = "a" AND b = "b" AND c = "c" ...
and get the one where the most matched? Of course after the WHERE clause, I wouldn't use the equal signs, but is there a way to try and get the most relevant table rows returned depending on what I'm searching for?
You might try MATCH AGAINST, but that requires a full text index, and I'm not sure it would even do this.
Another VERY SLOW way is to do something like:
SELECT a, b, c, d, e, f, g
FROM table
ORDER BY a = 'a', b = 'b', c = 'c', d = 'd', etc.
LIMIT 1
But this would mean a (very) weighted search too.
You could also use or, and then manually check how many matched.
-[Unknown]
the problem with:
SELECT a, b, c, d, e, f, g
FROM table
ORDER BY a = 'a', b = 'b', c = 'c', d = 'd', etc.
LIMIT 1
is that nothing might match, and therefore the most relevant row wouldn't show up
i was thinking a sort of permutation idea (not quite a permutation, but i don't know what the word would be).
- select all, and try to match against all, most relevant
- remove 1 column, and match against all rest, second most relevant (this would require me to remove 1 column each time, 5 columns = 5 queries)
- remove 2 columns, and match against all rest, third most relevant (this would require me to remove 2 columns each time...)
- and so on
doing permutations of what to remove each time, and as it goes down, to remove more. of course this would KILL resources if i had anything more than 5 columns, and even for 5 columns this is VERY heavy.
Just a little idea
SELECT a,b,c,IF(a='a',1,0)+IF(b='b',1,0)+IF(c='c',1,0) AS rel FROM testtab ORDER BY rel DESC LIMIT 1;
;D
i'll try that (but it looks to make sense). i've never done complicated statements like that. i'm sure it'll work, and therefore:
if i knew you in person, i'm sure i'd kiss you right about now :P LOL
WAAAAAAHHHHHHHH!!!! ;D
thank you VERY much SnowCrash :)
No problem... but I don't know if its very fast if your database contains a huge amount of data...
i can't imagine it'd be any slower... you aren't doing anything different from before, simply testing if values are equal, but instead of retrieving the value, just adding a number. I mean it's at least better than everything I suggested above, which seems to drain the system.
lucky for me i'm taking a sql course this year which deals with optimization. the problem is that it's an introductory course and right now (second week) we're only into nested SELECT statements.