Select values that meet different conditions on different rows in MySQL?

You can select values that meet different conditions on different rows using IN() and GROUP BY. The syntax is as follows −

SELECT yourColumnName1 from yourTableName
WHERE yourColumnName2 IN(value1,value2,.....N)
GROUP BY yourColumnName1
HAVING COUNT(DISTINCT yourColumnName2)=conditionValue;

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

mysql> create table DifferentRows
-> (
-> FirstRow int,
-> SecondRow int
-> );
Query OK, 0 rows affected (0.72 sec)

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

mysql> insert into DifferentRows values(10,10);
Query OK, 1 row affected (0.15 sec)

mysql> insert into DifferentRows values(10,100);
Query OK, 1 row affected (0.17 sec)

mysql> insert into DifferentRows values(10,300);
Query OK, 1 row affected (0.16 sec)

mysql> insert into DifferentRows values(20,100);
Query OK, 1 row affected (0.18 sec)

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

mysql> select *from DifferentRows;

The following is the output.

+----------+-----------+
| FirstRow | SecondRow |
+----------+-----------+
| 10       | 10        |
| 10       | 100       |
| 10       | 300       |
| 20       | 100       |
+----------+-----------+
4 rows in set (0.00 sec)

Here is the query to select value that meet different conditions on different rows. This gives all distinct FirstRow record that have SecondRow 10, 100, 300

mysql> select FirstRow from DifferentRows
-> where SecondRow IN(10,100,300)
-> group by FirstRow
-> having count(distinct SecondRow)=3;

The following is the output.

+----------+
| FirstRow |
+----------+
| 10       |
+----------+
1 row in set (0.00 sec)

Arjun Thakur

Published on 11-Jan-2019 10:42:49