- 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
Use JOIN to select record with more than one condition using AND?
Let us first create a demo table −
mysql> create table selectPerson -> ( -> PersonId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> PersonName varchar(20), -> PersonFavouriteFruit varchar(60) -> ); 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 selectPerson(PersonName,PersonFavouriteFruit) values('John','Banana'); Query OK, 1 row affected (0.14 sec) mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('John','Blackberry'); Query OK, 1 row affected (0.12 sec) mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('John','Blueberry'); Query OK, 1 row affected (0.12 sec) mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Carol','Apple'); Query OK, 1 row affected (0.19 sec) mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Carol','Avocado'); Query OK, 1 row affected (0.17 sec) mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Carol','Banana'); Query OK, 1 row affected (0.20 sec) mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Sam','Ackee'); Query OK, 1 row affected (0.15 sec) mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Sam','Apple'); Query OK, 1 row affected (0.89 sec) mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Sam','Apricots'); Query OK, 1 row affected (0.12 sec) mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Sam','Banana'); Query OK, 1 row affected (0.27 sec)
Display all records from the table using select statement. The query is as follows −
mysql> select *from selectPerson;
Here is the output −
+----------+------------+----------------------+ | PersonId | PersonName | PersonFavouriteFruit | +----------+------------+----------------------+ | 1 | John | Banana | | 2 | John | Blackberry | | 3 | John | Blueberry | | 4 | Carol | Apple | | 5 | Carol | Avocado | | 6 | Carol | Banana | | 7 | Sam | Ackee | | 8 | Sam | Apple | | 9 | Sam | Apricots | | 10 | Sam | Banana | +----------+------------+----------------------+ 10 rows in set (0.00 sec)
The following is the query to select persons who like Apple and Banana both −
mysql> SELECT tbl1.PersonName -> FROM selectPerson tbl1 JOIN selectPerson tbl2 on tbl1.PersonName =tbl2.PersonName -> WHERE -> tbl1.PersonFavouriteFruit='Banana' -> and -> tbl2.PersonFavouriteFruit='Apple';
The following is The output −
+------------+ | PersonName | +------------+ | Carol | | Sam | +------------+ 2 rows in set (0.00 sec)
- Related Articles
- Select MySQL rows where column contains same data in more than one record?
- Query nested array by more than one condition in MongoDB
- How to use MySQL JOIN without ON condition?
- How to select more than one row at a time in a JTable with Java?
- Use MongoDB Aggregate and select only top record (descending)
- Insert record using MySQL SELECT?
- Add more than one shadow to a text with CSS
- MySQL select * and find record with current date
- How to use if/else condition in select in MySQL?
- How to select first and last row record using LIMIT in MySQL?
- MySQL Select where value exists more than once
- Using more than one CSS classes for an element in HTML
- How to use spread operator to join two or more arrays in JavaScript?
- MySQL query to select a record with two exact values?
- Delete more than one rows from a table using id in MySQL?

Advertisements