Menu
Assume I have two tables:
cars – list of cars
carname | modelnumber | ... passedtest – contains every test that a car passed:
id | carname | testtype | date | ... 1 | carA | A | 2000 | 2 | carB | C | 2000 | 3 | carC | D | 2001 | 4 | carA | C | 2002 | Now, how can I select a car from the passedtest table that passed all tests (A, B, C, D)?
I tried the IN statement but it also matches cars that pass even one test. I am looking for a statement to match all values in a list across all rows.
358
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. The original idea was to create the table in beginning of the query, so the (SELECT * FROM #TBL) could be used on the query itself, instead of defining the names on each GROUP BY.
Actually, in MySQL you don't have to group by all columns. You can just group by whatever columns you want. The problem is, it will just pull a random value (from the set of available rows in the group) for the fields which aren't in the group by.
Many programmers continue to overlook helpful SQL Server features that have been available for years. Most of these overlooked features can simplify your queries, optimize their performance, and improve your productivity. One such feature is T-SQL's GROUP BY ALL option.
You have a few options: SELECT * FROM table WHERE id != 4; SELECT * FROM table WHERE NOT id = 4; SELECT * FROM table WHERE id <> 4; Also, considering perhaps sometime in the future you may want to add/remove id's to this list, perhaps another table listing id's which you don't want selectable would be a good idea.
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 ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
