Menu
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
280
Here's the generic SQL query to two compare columns (column1, column2) in a table (table1). mysql> select * from table1 where column1 not in (select column2 from table1); In the above query, update table1, column1 and column2 as per your requirement.
Use the LIKE or NOT LIKE comparison operators instead. The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP_LIKE() function (or the REGEXP or RLIKE operators, which are synonyms for REGEXP_LIKE() ).
MySQL LIKE with Percentage % Wildcard: >> SELECT TeachName, subject FROM data. teacher WHERE subject LIKE 'C%'; Use of the percentage sign before the pattern means that the pattern will match the last location of a value.
Using the SELECT Statement to Retrieve Data in SQL To retrieve multiple columns from a table, you use the same SELECT statement. The only difference is that you must specify multiple column names after the SELECT keyword, and separate each column by a comma.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
