How Can I Select Cars That Passed All Tests in a List Using SQL?

Filter rows that match all items in the list

Suppose you have two tables, "cars" and "passedtest", and you need a way to identify cars that have passed a specific set of tests. The "passedtest" table records the tests passed by each vehicle.

One way to achieve this is to use an IN statement. However, this method may not provide the desired results because it returns cars that even pass one of the tests in the specified list. To match all values ​​in the list in all rows another solution is needed.

The following SQL query utilizes GROUP BY and HAVING clauses to accomplish this task:

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

This query first groups the rows in the "passedtest" table by the "carname" column. It then uses the COUNT(DISTINCT) function to calculate the count of distinct "testtype" values ​​for each vehicle. The HAVING clause is used to filter the results to select only cars with a count of different test types equal to 4 (assuming there are four tests in the list).

You can also use the results of this query as a nested statement to extract additional information from the "cars" table:

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

This extended query will retrieve all relevant data from the "cars" table for vehicles that meet the specified criteria.

The above is the detailed content of How Can I Select Cars That Passed All Tests in a List Using SQL?. For more information, please follow other related articles on the PHP Chinese website!