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)