How to Find Common Values Across Multiple Rows in a MySQL Column?

Finding Common Values in MySQL Columns

When working with relational databases like MySQL, it's often necessary to retrieve rows that share identical column values. Consider a scenario where a table contains the following data:

ID	Score
1	95
2	100
3	88
4	100
5	73

Problem: How can we retrieve the Score that appears in both rows with IDs 2 and 4?

Solution:

This query uses the GROUP BY and HAVING clauses to achieve our desired result:

SELECT Score
FROM t
GROUP BY Score
HAVING SUM(id = 2) > 0 AND SUM(id = 4) > 0

Explanation:

• GROUP BY Score: This clause groups the rows by their Score column.
• HAVING SUM(id = 2) > 0: This part of the HAVING clause checks if the sum of rows with id = 2 is greater than zero within each group (Score).
• AND SUM(id = 4) > 0: This condition checks if the sum of rows with id = 4 is also greater than zero within the same group.

Result:

By combining these two conditions in the HAVING clause, we retrieve only the Score that is common to rows with both id = 2 and id = 4. In this case, it returns the value 100.

The above is the detailed content of How to Find Common Values Across Multiple Rows in a MySQL Column?. For more information, please follow other related articles on the PHP Chinese website!