Seleccionar filas que coincidan con todos los elementos en una lista

Supongamos que tengo dos tablas:

cars – lista de autos

 carname | modelnumber | ... 

passedtest – contiene todas las pruebas que aprobó un automóvil:

 id | carname | testtype | date | ... 1 | carA | A | 2000 | 2 | carB | C | 2000 | 3 | carC | D | 2001 | 4 | carA | C | 2002 | 

Ahora, ¿cómo puedo seleccionar un auto de la tabla de passedtest que pasó todas las pruebas (A, B, C, D)?

Probé la statement IN pero también coincide con los autos que pasan incluso una prueba. Estoy buscando una statement para hacer coincidir todos los valores en una lista en todas las filas.

¿Qué tal esto?

 SELECT carname FROM PassedTest GROUP BY carname HAVING COUNT(DISTINCT testtype) = 4 

También puede usarlo como una statement interna para tomar información de la mesa de cars :

 SELECT * FROM cars WHERE carname IN ( SELECT carname FROM PassedTest GROUP BY carname HAVING COUNT(DISTINCT testtype) = 4 ) 

Este tipo de problema se llama Relational Division .

 SELECT a.* FROM Cars a INNER JOIN ( SELECT CarName FROM PassedTest WHERE testType IN ('A', 'B', 'C', 'D') GROUP BY CarName HAVING COUNT(*) = 4 ) b ON a.CarName = b.CarName 

si una restricción UNIQUE no se TestType en TestType para cada CarName en la tabla PassedTest se PassedTest una palabra clave DISTINCT en COUNT() por lo que solo contará los valores únicos.

 SELECT a.* FROM Cars a INNER JOIN ( SELECT CarName FROM PassedTest WHERE testType IN ('A', 'B', 'C', 'D') GROUP BY CarName HAVING COUNT(DISTINCT TestType) = 4 ) b ON a.CarName = b.CarName 
  • SQL de la división relacional

pero si solo está interesado en el CARNAME entonces no necesita unirse a las tablas. La consulta en la tabla PassedTest se PassedTest a sus necesidades.

 SELECT CarName FROM PassedTest WHERE testType IN ('A', 'B', 'C', 'D') GROUP BY CarName HAVING COUNT(*) = 4 

Desea realizar una división relacional, una operación que no está implementada en SQL. Aquí hay un ejemplo donde tenemos una tabla de proveedor de producto y una tabla de productos requeridos :

 CREATE TABLE product_supplier ( product_id int NOT NULL, supplier_id int NOT NULL, UNIQUE (product_id, supplier_id) ); INSERT INTO product_supplier (product_id, supplier_id) VALUES (1, 1), (2, 1), (3, 1), (1, 2), (2, 2), (3, 2), (4, 2), (2, 3), (3, 3), (4, 3); CREATE TABLE reqd ( product_id int NOT NULL, UNIQUE (product_id) ); INSERT INTO reqd (product_id) VALUES (1), (2), (3); 

… y queremos encontrar todos los proveedores que suministren TODOS los productos requeridos y tal vez otros. El resultado en el ejemplo anterior sería el proveedor 1 y 2.

La solución más directa es esta:

 SELECT product_supplier.supplier_id FROM product_supplier LEFT JOIN reqd ON product_supplier.product_id = reqd.product_id GROUP BY product_supplier.supplier_id HAVING COUNT(reqd.product_id) = (SELECT COUNT(*) FROM reqd); 
 +-------------+ | supplier_id | +-------------+ | 1 | | 2 | +-------------+ 

Y si queremos encontrar todos los proveedores que suministren TODOS los productos requeridos y no otros (división exacta / sin residuo), agregue una condición más a la anterior:

 SELECT product_supplier.supplier_id FROM product_supplier LEFT JOIN reqd ON product_supplier.product_id = reqd.product_id GROUP BY product_supplier.supplier_id HAVING COUNT(reqd.product_id) = (SELECT COUNT(*) FROM reqd) AND COUNT(product_supplier.product_id) = (SELECT COUNT(*) FROM reqd); 
 +-------------+ | supplier_id | +-------------+ | 1 | +-------------+ 

Una solución alternativa es reformular el problema: seleccionar proveedores donde no exista un producto requerido que no exista en los productos suministrados por el proveedor. Hmmm:

 SELECT DISTINCT supplier_id FROM product_supplier AS ps1 WHERE NOT EXISTS ( SELECT * FROM reqd WHERE NOT EXISTS ( SELECT * FROM product_supplier AS ps2 WHERE ps1.supplier_id = ps2.supplier_id AND ps2.product_id = reqd.product_id ) ); 
 +-------------+ | supplier_id | +-------------+ | 1 | | 2 | +-------------+