Select all records in which at least n-1 of n criteria has been matched

Question

I'm using Oracle 12c. I'm wondering if it is possible to select all records in which n-1 of n criteria has been matched ?

Example:

CREATE TABLE users
(id number, 
firstname varchar2(100), 
lastname varchar2(100), 
city  varchar2(100));

insert into users(id, firstname, lastname, city)
 values (1, 'John', 'Smith', 'London');
insert into users(id, firstname, lastname, city)
 values (2, 'Tom',  'Smith', 'London');
insert into users(id, firstname, lastname, city)
 values (3, 'John', 'Davis', 'London');
insert into users(id, firstname, lastname, city)
 values (4, 'John', 'Smith', 'Bristol');
insert into users(id, firstname, lastname, city)
 values (5, 'Tom',  'Davis', 'London');
insert into users(id, firstname, lastname, city)
 values (6, 'Tom',  'Davis', 'Bristol');

 select * from users 
  where firstname = 'John' 
    and lastname = 'Smith'
    and city= 'London'

This select will return only one record that match all three criteria (id = 1). What I need is a query that returns all the records that match at least two of three criteria (id = 1, 2, 3, 4).

Is it possible in Oracle if we know that users table has 5 milions records ?

Answer 1

General approach is to put each condition in a CASE returning 1 or 0, and count the number of 1s:

select * from users 
where (CASE WHEN firstname = 'John' THEN 1 ELSE 0 END
    + CASE WHEN lastname = 'Smith' THEN 1 ELSE 0 END
    + CASE WHEN city= 'London' THEN 1 ELSE 0 END) >= 2

Each matching condition contributes 1 to the sum, so you can check how many conditions have been satisfied.

Answer 2

You can use expressions in the where clause:

select *
from users 
where ( (case when firstname = 'John' then 1 else 0 end) +
        (case when lastname = 'Smith' then 1 else 0 end) +
        (case when city = 'London' then 1 else 0 end)
      ) = 2;

This generalizes easily, but for 3 conditions and 2 matches, it is easy enough to do:

where (firstname = 'John' and lastname = 'Smith' and city <> 'London') or
      (firstname = 'John' and lastname <> 'Smith' and city = 'London') or
      (firstname <> 'John' and lastname = 'Smith' and city = 'London')

However, this doesn't general very nicely.