Select group of rows that match all items in a list

by

How about this?

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

You can also use it as an inner statement for taking info from the cars table:

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

More Related Contents:

  1. Calculate a Running Total in SQL Server
  2. How can I do an UPDATE statement with JOIN in SQL Server?
  3. Subquery using Exists 1 or Exists *
  4. Truncate (not round) decimal places in SQL Server
  5. Most efficient T-SQL way to pad a varchar on the left to a certain length?
  6. Dynamically create columns sql
  7. Reference alias (calculated in SELECT) in WHERE clause
  8. TSQL PIVOT MULTIPLE COLUMNS
  9. When to use Common Table Expression (CTE)
  10. INT vs Unique-Identifier for ID field in database
  11. How to send email from SQL Server?
  12. How To Create Table with Identity Column
  13. Why do you create a View in a database?
  14. SQL query to find Nth highest salary from a salary table
  15. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  16. Calculate the number of records for each date between 2 dates
  17. T-SQL: How to use parameters in dynamic SQL?
  18. Get the records of last month in SQL server
  19. How to compare datetime with only date in SQL Server
  20. Merge overlapping date intervals
  21. SQL server select distinct rows using most recent value only
  22. Access to Result sets from within Stored procedures Transact-SQL SQL Server
  23. alternatives to REPLACE on a text or ntext datatype
  24. Split words with a capital letter in sql
  25. SQL poor stored procedure execution plan performance – parameter sniffing
  26. Eliminate and reduce overlapping date ranges
  27. scope_identity vs ident_current
  28. COALESCE Function in TSQL
  29. Convert bigint to datetime
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment