SQL query results returned, even if exact match not found

Question

I hope this question isn't redundant. What I am trying to accomplish is have a user select a bunch of checkboxes on a page and return the closest matching records if there are no matching rows. For example:

A person checks off [x]Apples [x]Oranges [x]Pears [x]Bananas

But the table looks like this:

Apples     Oranges      Pears     Bananas
 1             1           1        null
 1             1         null       1
 1             1         null       null

(Obviously I missed the id column here, but you get the point I think.) So, the desired result is to have those three rows still be returned in order of most matches, so pretty much the order they are in now. I'm just not sure what the best approach to take on something like this. I've considered a full text search, the levenshtein function, but I really like the idea of returning the exact match if it exists. No need for you to go at length with code if not needed. I'm just hoping to be sent in the right direction. I HAVE seen other questions sort of like this, but I still am unsure about which way to go.

Thanks!

Not sure if I understand correctly, but if I do it just works wit ``ORDER BY orances DESC, pears DESC, bananas DESC`` — Tim, Oct 31 '12 at 23:09
Can the table contain values other than 1 or NULL in those columns? If a match is considered positively, should a mis-match be considered negatively? Do you want to treat NULL as matching everything or matching nothing? Perhaps it means UNKNOWN, perhaps it means MISSING? — MatBailie, Oct 31 '12 at 23:29
Null for my purposes is effectively 0. I'm trying to do a binary system. Either 1 if the column has been tagged, or null if it hasn't been yet. My intent is to not have the null values rouse suspicion or what not. — user1789907, Oct 31 '12 at 23:43

score 2 · Answer 1 · answered Oct 31 '12 at 23:05

2

Write a query that adds up the number of columns that matched, and sorts the rows by this total. E.g.

SELECT *
FROM mytable
ORDER BY COALESCE(Apples, 0) = $apples + COALESCE(Oranges, 0) = $oranges + ... DESC

answered Oct 31 '12 at 23:05

Barmar

741,623
53
500
612

score 1 · Answer 2 · edited May 23 '17 at 12:03

It's easy to sort by a score...

SELECT fb.ID, fb.Apples, fb.Oranges, fb.Pears, fb.Bananas
FROM FruitBasket fb
ORDER BY
  CASE WHEN @Apples = fb.Apples THEN 1 ELSE 0 END
  + CASE WHEN @Oranges = fb.Oranges THEN 1 ELSE 0 END
  + CASE WHEN @Pears = fb.Pears THEN 1 ELSE 0 END
  + CASE WHEN @Bananas = fb.Bananas THEN 1 ELSE 0 END
  DESC, ID

However, this leads to a table-scan (even with TOP). The last record may be a better match than the records found so far, so every record must be read.

You could consider a tagging system, like this

Content --< ContentTag >-- Tag

Which would be queried this way:

SELECT ContentID
FROM ContentTag
WHERE TagID in (334, 338, 342)
GROUP BY ContentID
ORDER BY COUNT(DISTINCT TagID) desc

An index on ContentTag.TagId would be used by this query.

doublesharp · Answer 3 · 2012-10-31T23:33:35.630

0

This is fairly simple, but you can just use IFNULL() (MySQL, or your DB's equivalent) to return a sum of matches and use that in your ORDER BY

// columns and weighting score
$types = array("oranges"=>1, "apples"=>1, "bananas"=>1, "pears"=>1);
$where = array();
// loop through the columns
foreach ($types as $key=>&$weight){
    // if there is a match in $_REQUEST at it to $where and increase the weight
    if (isset($_REQUEST[$key])){
        $where[] = $key . " = 1";
        $weight = 2;
    }
}
// build the WHERE clause
$where_str = (count($where)>0)? "WHERE " . implode(" OR ", $where) : "";

// build the SQL - non-null matches from the WHERE will be weighted higher
$sql = "SELECT apples, oranges, pears, bananas, ";
foreach ($types as $key=>$weight){
    $sql .= "IFNULL({$key}, 0, {$weight}) + ";
} 
$sql .= "0 AS score FROM `table` {$where_str} ORDER BY score DESC";

Assuming that "oranges" and "apples" are selection, your SQL will be:

SELECT apples, oranges, pears, bananas, 
IFNULL(apples, 0, 2) + IFNULL(oranges, 0, 2) + IFNULL(pears, 0, 1) + IFNULL(bananas, 0, 1) + 0 AS score 
FROM `table` 
WHERE oranges = 1 OR apples = 1 
ORDER BY score DESC

edited Oct 31 '12 at 23:33

answered Oct 31 '12 at 23:07

doublesharp

26,888
6
52
73

How is this using the input from the checkboxes? – Barmar Oct 31 '12 at 23:09
Okay, sweet! I'm going to dig into this, do some research following your guys' lead. I'll let you know how I fair. Thank you for the timely response. Appreciated. – user1789907 Oct 31 '12 at 23:10
I was planning on passing the variables through with php somehow, lol. – user1789907 Oct 31 '12 at 23:11
I'm making a job site. I probably should have specified that. So the user selects their degrees they possess and then jobs tagged with those degrees are returned. – user1789907 Oct 31 '12 at 23:14
@user1789907 because i have too much time on my hands, see the edit. – doublesharp Oct 31 '12 at 23:31
Haha awesome. I'm diving into it now. – user1789907 Oct 31 '12 at 23:48

score 0 · Answer 4 · answered Oct 31 '12 at 23:42

0

Order descending by the sum of checkbox/data matches

SELECT * FROM table
ORDER BY (COALESE(Apple,0) * @apple) + (COALESE(Orange,0) * @orange) ..... DESC

where @apple / @orange represents users selection: 1 = checked, 0 = unchecked

answered Oct 31 '12 at 23:42

Lukas Winzenried

1,919
1
14
22

SQL query results returned, even if exact match not found

4 Answers4