CODEWITHSUNDEEP
mysql
Joanne
Joanne

Reputation: 315

MySQL matching 2 out of 5 fields

In MySQL I'm trying to select any row that matches at least 2 fields of the provided data

Eg. I have been given firstName, lastName, dob, website, email and I want any rows that match firstName and lastName, or firstName and email, or website and email etc.

I know I could write a very long winded statement along the lines of (this=this AND this=this) OR (this=this etc but this query could potentially get really large, especially if we decide we want to match on more than 5 fields.

We will also need to rank the matching rows, so if some rows match 3 instead of only the minimum 2 fields then they should show up higher in the returned results.

I could process this afterwards with PHP, or do multiple SQL queries, I'm just wondering if anyone knows an easier/cleaner way to match this data?

I appreciate any help! Jo

Upvotes: 12

Views: 1670

Answers (3)

Sam DeHaan
Sam DeHaan

Reputation: 10325

As a'r's answer recommended, you can add together values. If you want to use this for ranking, you might not include it in the where clause as he did.

SELECT *, ((firstName = @inputFirst) + (lastName = @inputLast) + (dob = @inputDob) + (website = @inputWebsite) + (email = @inputEmail)) as Matches
FROM mytable
HAVING Matches > 1
ORDER BY Matches DESC

I don't have access to a mysql db to test this syntax at the moment, but I believe it should work properly.

Upvotes: 4

Andy
Andy

Reputation: 3743

SELECT
(CASE WHEN Field1 = Value1 THEN 1 ELSE 0 END
+CASE WHEN Field2 = Value2 THEN 1 ELSE 0 END
...
+CASE WHEN FieldN = ValueN THEN 1 ELSE 0 END
)
AS Conditions
From YourTable
WHERE Conditions >= (Min_Number_Of_Fields)

Upvotes: -1

a'r
a'r

Reputation: 37009

You could count up the matching expressions. MySQL returns 1 for true and 0 for false.

WHERE (FirstName = ?) + (LastName = ?) + (... = ?) > 2

You can also order using this as well. You will want to sort descending to ensure that the higher matches appear first.

ORDER BY ((FirstName = ?) + (LastName = ?) + (... = ?)) DESC

Upvotes: 12

Related Questions