MySQL Query - Order by best matches

Databases
1

Hi All,

I’m certainly no mySQL guru so i’m looking for some assistance with a query. The query below brings back 1 exact match fine but the data in the table is not fully complete. What I want to do is return the all results in order of ‘all 5 match’, ‘4 match’, ‘3 match’, ‘2 match’, ‘1 match’.

SELECT * FROM master WHERE
make_id=$make AND
model_id=$model AND
variant_id=$variant AND
engine_id=$engine AND
issueyear_id=$issueyear
LIMIT 1"

I hope I’ve explained it ok! Any help would be appreciated. Thx in advance.

Pete

2

To get all results, get rid of the LIMIT

To order them based on the number of matching columns is a bit trickier I think. No time to think about it now, maybe someone else knows the solution?

3

Thanks for the advice, I’ve gone down the multiple queries and PHP route for know but will look around to see if i can get it down to the 1 query.

Pete

4 

select <columnsOfInterest>,
       case when make_id = $make then 1 else 0 end + 
       case when model_id = $model then 1 else 0 end + 
       case when variant_id = $variant then 1 else 0 end + 
       case when engine_id = $engine then 1 else 0 end + 
       case when issueyear_id = $issueyear then 1 else 0 end as matches 
  from master 
 where make_id = $make 
    or model_id = $model
    or variant_id = $variant 
    or engine_id = $engine
    or issueyear_id = $issueyear
 order by matches desc