CODEWITHSUNDEEP
mysql
Martin
Martin

Reputation: 22760

MySQL finding data if any 4 of 5 columns are found in a row

I have an imported table of several thousand customers, the development I am working on runs on the basis of anonymity for purchase checkouts (customers do not need to log in to check out), but if enough of their details match the database record then do a soft match and email the (probably new) email address and eventually associate the anonymous checkout with the account record on file.

This is rolling out this way due to the age of the records, many people have the same postal address or names but not the same email address, likewise some people will have moved house and some people will have changed name (marriage etc).

What I think I am looking for is a MySQL CASE system, however the CASE questions on Stack Overflow I've found don't appear to cover what I'm trying to get from this query.

The query should work something like this:

$input[0] = postcode (zip code)
$input[1] = postal address
$input[2] = phone number
$input[3] = surname
$input[4] = forename

SELECT account_id FROM account WHERE <4 or more of the variables listed match the same row>

The only way I KNOW I can do this is with a massive bunch of OR statements but that's excessive and I'm sure there's a cleaner more concise method.

I also apologise in advance if this is relatively easy but I don't [think I] know the keyword to research constructing this. As I say, CASE is my best guess.

I'm having trouble working out how to manipulate CASE to fit what I'm trying to do. I do not need to return the values only the account_id from the valid row (only) that matches 4 or 5 of the given inputs.

I imagine that I could construct a layout that does this:

SELECT account_id CASE <if postcode_column=postcode_var> X=X+1
                  CASE <if surname_column=surname_var> X=X+1
                  ...
                  ...
                  WHERE X > 3

Upvotes: 1

Views: 37

Answers (1)

Alex
Alex

Reputation: 17289

Here is your pseudo query:

SELECT account_id 
FROM account 
WHERE (postcode = 'pc')+
       (postal_address = 'pa')+
       (phone_number = '12345678901')+
       (surname = 'sn')+
       (forename= 'fn') > 3

Upvotes: 3

Related Questions