CodingForums.com - View Single Post - Getting the nearest SQL results if a query cannot find an exact match?

guelphdad · 10-10-2012, 01:17 PM

First if you have exact values in your fields you should not be searching with LIKE beginning with %. This prevents your search from using an index and thus is forced to do a table scan. No indexes can be used on your search because every single search begins with %. That is the first thing to fix.

Now to clarify your question, do you mean that for every single column you might be searching for a value and that value might not exist?

What return would satisfy your query if you are looking for width 100, height 100, depth 100:

Code:

Width   Height   Depth
90        100       100
100        90        90
110       100       100
90         90        90

is there a particular one that you would want returned since each column doesn't match the specs searched for? If a particular one should be returned what is the logic behind returning that row and not one of the others?

10-10-2012, 01:17 PM	PM User \| #2
guelphdad Super Moderator Join Date: Mar 2006 Location: St. Catharines, Ontario Canada Posts: 2,629 Thanks: 4 Thanked 147 Times in 138 Posts	First if you have exact values in your fields you should not be searching with LIKE beginning with %. This prevents your search from using an index and thus is forced to do a table scan. No indexes can be used on your search because every single search begins with %. That is the first thing to fix. Now to clarify your question, do you mean that for every single column you might be searching for a value and that value might not exist? What return would satisfy your query if you are looking for width 100, height 100, depth 100: Code: Width Height Depth 90 100 100 100 90 90 110 100 100 90 90 90 is there a particular one that you would want returned since each column doesn't match the specs searched for? If a particular one should be returned what is the logic behind returning that row and not one of the others? Last edited by guelphdad; 10-10-2012 at 01:20 PM..