CODEWITHSUNDEEP
mysqlsql
Ank
Ank

Reputation: 6270

Retrieve items from table that has value1 but not value2

I have a table t1 (below is a very simplified version of real table)

Name Subject Pass
A    Math    Yes
A    English  No
A    Science  No
B    Math     No
B    English  No
B    Science  No

I need to query all names that haven't passed in any subject (pass = no)

`Select distinct(name) from t1 where Pass = 'No' and Pass <> 'Yes' doesn't work

Upvotes: 0

Views: 76

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

I would use aggregation. Assuming pass only takes on those two values:

select name
from t1
group by name
having min(pass) = 'No' and max(pass) = 'No';

This checks that the only pass values for a given name is 'No' -- the minimum and maximum are the same.

If you have a separate table of names, a more efficient version is probably:

select n.name
from names n
where not exists (select 1 from t1 where t1.name = n.name and t1.pass = 'Yes');

This works best with an index on t1(name, pass).

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Your title and description don't really match - but I assume you're after something like this (the old school approach)...

SELECT DISTINCT x.name  
           FROM my_table x  
           LEFT 
           JOIN my_table y 
             ON y.name = x.name 
            AND y.pass = 1 -- or 'yes', if you insist
          WHERE y.name IS NULL

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562368

Select distinct(name) from t1 where Pass = 'No' and Pass <> 'Yes'

This doesn't work because the expression in the WHERE clause is evaluated for one row at a time, not for the whole group. Your Pass column can't simultaneously have values of 'No' and 'Yes' on any given row, therefore the query above will only filter for rows where Pass = 'No' regardless of whether there is some other row with 'Yes'.

For a solution to your query, you could write it this way:

SELECT name
FROM t1
GROUP BY name
HAVING SUM(Pass = 'Yes') = 0

Upvotes: 3

Related Questions