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

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)
Updated on: 2019-07-30T22:30:25+05:30

458 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements