How to Retrieve Matching Column Values in MySQL Using Aggregation and HAVING?

Efficiently Retrieving Matching Column Values in MySQL

Database queries often require retrieving rows based on specific column value matches. This task becomes more complex when dealing with large tables. This example demonstrates an efficient method using MySQL's aggregation functions and the HAVING clause.

Let's consider a sample table:

<code>ID | Score
-----
1 | 95
2 | 100
3 | 88
4 | 100
5 | 73</code>

The goal is to find scores common to both ID 2 and ID 4. The expected result is 100.

MySQL's aggregation capabilities provide an elegant solution:

<code class="language-sql">SELECT score
FROM t
GROUP BY score
HAVING SUM(id = 2) > 0 AND SUM(id = 4) > 0;</code>

This query groups the data by score. The HAVING clause then uses aggregate functions to check for the presence of both ID 2 and ID 4 within each group. SUM(id = 2) > 0 counts occurrences of ID 2, and SUM(id = 4) > 0 counts occurrences of ID 4. Only scores where both counts are greater than zero are returned. This ensures that only scores associated with both IDs are included in the result.

The above is the detailed content of How to Retrieve Matching Column Values in MySQL Using Aggregation and HAVING?. For more information, please follow other related articles on the PHP Chinese website!