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 06:12:49

• Related Questions & Answers
• Count values based on conditions and display the result in different columns with MySQL?
• Concatenate two values from the same column with different conditions in MySQL
• How to select different values from same column and display them in different columns with MySQL?
• SET only two values for all the rows in a MySQL table based on conditions?
• Fetch a value between different values in MySQL
• How to select and display a list of values in one column that are available in two different MySQL columns?
• Get rows that have common value from the same table with different id in MySQL
• MySQL query to select a count on two separate conditions?
• Count only null values in two different columns and display in one MySQL select statement?
• Python Pandas – How to select DataFrame rows on the basis of conditions
• Stretch single rows of items on different screens in Bootstrap 4
• Select count of values (Yes, No) with same ids but different corresponding records in MySQL?
• Set different IDs for records with conditions using a single MySQL query
• Different Operations on Processes
• What are the different unit values that can be used with MySQL INTERVAL keyword?