Menu
I want to select the tests that run on IE7 and not run on IE8. I tried this but i get 0 and this is not true.
SELECT test_name
FROM tests
WHERE version='ie7'
AND NOT EXISTS (SELECT test_name FROM tests where version='ie8');
Thank you!
866
To test whether a row exists in a MySQL table or not, use exists condition. The exists condition can be used with subquery. It returns true when row exists in the table, otherwise false is returned. True is represented in the form of 1 and false is represented as 0.
If EXISTS (subquery) returns at least 1 row, the result is TRUE. If EXISTS (subquery) returns no rows, the result is FALSE. If NOT EXISTS (subquery) returns at least 1 row, the result is FALSE. If NOT EXISTS (subquery) returns no rows, the result is TRUE.
“IN” clause is preferred when there is a small list of static values or the inner query returns a very less number of rows. “EXISTS” clause is preferred when there is a need to check the existence of values in another table or when there is a need to check against more than one column.
Try this
SELECT test_name
FROM tests
WHERE version='ie7'
AND test_name NOT In (SELECT test_name FROM tests where version='ie8');
You probably mean:
SELECT test_name
FROM tests t1
WHERE version='ie7'
AND NOT EXISTS (SELECT test_name FROM tests t2 where test_name = t1.test_name AND version='ie8');
(My Transact-SQL is a bit rusty, but I think this is how it is done. The important thing is that you were saying "I want all test_names with version 'ie7' and also that no row exists in the database at all with version 'ie8'" :) )
25
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
