Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
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)
Updated on: 30-Jul-2019
40 Views
Advertisements
To Continue Learning Please Login