| AID | BID | STATE |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 2 | 3 |
| 1 | 3 | 3 |
| 2 | 1 | 0 |
| 2 | 2 | 3 |
| 2 | 3 | 3 |
| 3 | 1 | 3 |
| 3 | 2 | 0 |
| 3 | 3 | 3 |
I am trying to find AID records which doesnt have any 0 state in this example AID = 1 (will be multiple records)
CodePudding user response：
If you would like to find all the AID with no 0 state records you may use
SELECT
AID
FROM
mytable
GROUP BY
AID
HAVING
COUNT(
CASE WHEN STATE=0 THEN 1 END
)=0;
| AID |
|---|
| 1 |
or if you would like to find all AID records where the state is not 0 for any AID record you may use the following.
SELECT
*
FROM
mytable
WHERE AID NOT IN (
SELECT AID FROM mytable WHERE STATE=0
)
| AID | BID | STATE |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 2 | 3 |
| 1 | 3 | 3 |
Let me know if this works for you.