Extract tuples with specified common values in another column in MySQL?

To extract tuples with specified common values, use the following syntax −

SELECT DISTINCT
aliasName.yourColumnName1,aliasName.yourColumnName2,aliasName1.yourColumnName
1,aliasName1.yourColumnName2
FROM yourTableName aliasName
INNER JOIN yourTableName aliasName1
ON aliasName.yourColumnName1 = aliasName1.yourColumnName1
WHERE aliasName.yourColumnName2 = 'value1' AND aliasName1.yourColumnName2 =
'value2';

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table extractTuples
   -> (
   -> Id int,
   -> Name varchar(20),
   -> Comments text
   -> );
Query OK, 0 rows affected (0.77 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into extractTuples values(1,'John','hi');
Query OK, 1 row affected (0.20 sec)
mysql> insert into extractTuples values(2,'Carol','hello');
Query OK, 1 row affected (0.17 sec)
mysql> insert into extractTuples values(3,'John','Amazing');
Query OK, 1 row affected (0.13 sec)
mysql> insert into extractTuples values(1,'Carol','Good');
Query OK, 1 row affected (0.14 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> select *from extractTuples;

Output

+------+-------+----------+
| Id   | Name  | Comments |
+------+-------+----------+
|    1 | John  | hi       |
|    2 | Carol | hello    |
|    3 | John  | Amazing  |
|    1 | Carol | Good     |
+------+-------+----------+
4 rows in set (0.00 sec)

Here is the query to extract tuples with specified common values −

mysql> SELECT DISTINCT tbl.Id,tbl.Name,tbl1.Id,tbl1.Name
   -> FROM extractTuples tbl
   -> INNER JOIN extractTuples tbl1
   -> ON tbl.Id = tbl1.Id
   -> WHERE tbl.Name = 'John' AND tbl1.Name = 'Carol';

Output

+------+------+------+-------+
| Id   | Name | Id   | Name  |
+------+------+------+-------+
|    1 | John |    1 | Carol |
+------+------+------+-------+
1 row in set (0.00 sec)