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)

Samual Sam

Published on 01-Apr-2019 06:14:46

• Related Questions & Answers
• 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 select more than one row at a time in a JTable with Java?
• Add more than one shadow to a text with CSS
• How to use MySQL JOIN without ON condition?
• Use MongoDB Aggregate and select only top record (descending)
• MySQL Select where value exists more than once
• Using more than one CSS classes for an element in HTML
• Insert record using MySQL SELECT?
• MySQL select * and find record with current date
• Delete more than one rows from a table using id in MySQL?
• Select rows having more than 2 decimal places in MySQL?
• Python Pandas – Remove leading and trailing whitespace from more than one column
• How to use if/else condition in select in MySQL?
• MySQL search if more than one string contains special characters?