MYSQL: Can you pull results that match like 3 out of 4 expressions?

You can use CASE statement to get the results that match some expressions−

SELECT *FROM yourTableName
WHERE CASE WHEN yourColumnName1 = yourValue1 THEN 1 ELSE 0 END +
   CASE WHEN yourColumnName2 = yourValue2 THEN 1 ELSE 0 END +
   CASE WHEN yourColumnName3 = yourValue3 THEN 1 ELSE 0 END +
   .
   .
CASE WHEN yourColumnNameN = yourValueN THEN 1 ELSE 0 END > = 3;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table UserInformation
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> FirstName varchar(20),
   -> LastName varchar(20),
   -> Age int,
   -> Marks int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.58 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into UserInformation(FirstName,LastName,Age,Marks) values('John','Smith',22,89);
Query OK, 1 row affected (0.12 sec)

mysql> insert into UserInformation(FirstName,LastName,Age,Marks) values('Carol','Taylor',21,80);
Query OK, 1 row affected (0.19 sec)

mysql> insert into UserInformation(FirstName,LastName,Age,Marks) values('John','Doe',24,81);
Query OK, 1 row affected (0.14 sec)

mysql> insert into UserInformation(FirstName,LastName,Age,Marks) values('David','Miller',29,99);
Query OK, 1 row affected (0.15 sec)

mysql> insert into UserInformation(FirstName,LastName,Age,Marks)
values('Mitchell','Johnson',22,65);
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from UserInformation;

The following is the output.

+----+-----------+----------+------+-------+
| Id | FirstName | LastName | Age  | Marks |
+----+-----------+----------+------+-------+
|  1 | John      | Smith    |   22 | 89 |
|  2 | Carol     | Taylor   | 21 | 80 |
|  3 | John      | Doe      | 24 | 81 |
|  4 | David     | Miller   | 29 | 99 |
|  5 | Mitchell  | Johnson  | 22 | 65 |
+----+-----------+----------+------+-------+
5 rows in set (0.00 sec)

Here is the query to pull out the record that matches some (not all) expression. The query is as follows−

mysql> select *from UserInformation
   -> where case when FirstName = 'Mitchell' then 1 else 0 end +
   -> case when LastName = 'Johnson' then 1 else 0 end +
   -> case when Age = 22 then 1 else 0 end +
   -> case when Marks = 67 then 1 else 0 end > = 3;

The following is the output−

+----+-----------+----------+------+-------+
| Id | FirstName | LastName | Age  | Marks |
+----+-----------+----------+------+-------+
|  5 | Mitchell  | Johnson  | 22   | 65    |
+----+-----------+----------+------+-------+
1 row in set (0.00 sec)

Arjun Thakur

Published on 01-Feb-2019 12:32:03

Previous Page Print Page