How to Find Cars That Passed All Tests (A, B, C, D) in a Database?

Filter row groups that match all items in the list

Suppose there are two tables:

• cars: List cars and their corresponding models.
• passedtest: Records the test passed by each vehicle, including its unique ID, vehicle name, test type and date.

Question:

How to identify cars that passed all tests (A,B,C,D) from the passedtest table?

Limitations of the IN statement:

Using an IN statement to match lines containing a list of tests may return cars that pass only a subset of those tests.

Solution: Group and count different test types

This solution utilizes COUNT() and the HAVING clause:

<code class="language-sql">SELECT carname
FROM PassedTest
GROUP BY carname
HAVING COUNT(DISTINCT testtype) = 4;</code>

This query groups rows by carname and counts the number of distinct testtype values. A car with a count of 4 has passed all four tests.

Additional Tips:

To retrieve data from the cars table based on the result set, use this extended query:

<code class="language-sql">SELECT *
FROM cars
WHERE carname IN (
    SELECT carname
    FROM PassedTest
    GROUP BY carname
    HAVING COUNT(DISTINCT testtype) = 4
);</code>

The above is the detailed content of How to Find Cars That Passed All Tests (A, B, C, D) in a Database?. For more information, please follow other related articles on the PHP Chinese website!