CODEWITHSUNDEEP
mysqlsql
Emil Bonne Kristiansen
Emil Bonne Kristiansen

Reputation: 242

Select occurrences that doesn't have a certain value in field

Given a table with fields A and B:

| A | B |
---------
| 1 | p |
| 1 | f |
| 1 | t |
| 2 | p |
| 2 | f |

I am trying to construct a query finding all the A's that doesn't also have a 't' for B somewhere.

So for this data the output should just be

| A |
-----
| 2 |

as 2 doesn't have 't' listed anywhere in field B

I tried doing SELECT DISTINCT A FROM table WHERE B!='t', but that logic is flawed as 1 also contains a row with B!='t'. I also tried various variations of GROUP BY, but I am stuck.

Please help

Upvotes: 4

Views: 91

Answers (3)

Mansoor
Mansoor

Reputation: 4192

Use GROUP BY AND HAVING Clause :

 SELECT A
 FROM 
 GROUP BY A
 HAVING SUM(B = 't') = 0;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would use group by and having for this:

select a
from t
group by a
having sum(b = 't') = 0;

Upvotes: 5

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT A
FROM mytable
GROUP BY A
HAVING SUM(B = 't') = 0

The HAVING clause filters out A groups that contain at least one record with B = 't'.

Upvotes: 4

Related Questions