- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- Use MySQL LIKE and NOT LIKE to display similar results?
- Can you allow a regex match in a MySQL Select statement?
- How can we match the values having backslashes, like ‘ab’, from MySQL column?
- Finding a Match Within Another Match Java regular expressions
- MySQL Regular expressions: How to match digits in the string with d?
- Simplify the following expressions:$(4+sqrt7)(3+sqrt2)$
- How do you OR two MySQL LIKE statements?
- What are the different wildcard characters that can be used with MySQL LIKE operator?
- Query results that have less than X characters in MySQL?
- How to use MySQL LIKE to create a search system and display results on the basis of keyword?
- How do you force MySQL LIKE to be case sensitive?
- MySQL Regex to match a pattern for ignoring a character in search like Chris.Brown?
- How can we force MySQL out of TRADITIONAL mode?
- How the MySQL command that you are in the process of entering can be canceled?
- What are some features of Pandas in Python that you like or dislike?

Advertisements