Help with MySQL query to select only records with specific value in column

Question

I have following table:

+--------+-----------+--------+
|   ID   |  Title    |  Stat  |
+--------+-----------+--------+
|    1   |  title a  |    1   |
+--------+-----------+--------+
|    1   |  title b  |    0   |
+--------+-----------+--------+
|    2   |  title c  |    1   |
+--------+-----------+--------+
|    2   |  title d  |    1   |
+--------+-----------+--------+
|    3   |  title e  |    0   |
+--------+-----------+--------+
|    3   |  title f  |    1   |
+--------+-----------+--------+
|    3   |  title g  |    1   |
+--------+-----------+--------+

I need to construct a query, where as a result only those records would be included where ALL values in column Stat meet certain condition.

For above table, how to formulate a query that would basically mean: show me records for only those ID's that have all the values in column Stat = 1 and would as a result produce following table?

+--------+-----------+--------+
|    2   |  title c  |    1   |
+--------+-----------+--------+
|    2   |  title d  |    1   |
+--------+-----------+--------+

Thanks.

Answer 1

Something like this?

SELECT
  *
FROM table t
LEFT JOIN (
  SELECT
    MIN(Stat) AS Stat,
    ID
  FROM table
  GROUP BY ID
) minstat ON t.ID = minstat.ID
WHERE minstat.Stat = 1;

Basically, the subquery computes the minimum stat per ID, whereas the outer query only selects the rows with the minimal stat equal to 1. If all rows need to satisfy a certain condition, insert a boolean expression in stead of Stat in the minimum.

Not sure if this is possible without a subquery.

Answer 2

This also works,

SELECT * FROM tablename t1  
WHERE (
        SELECT COUNT(*) FROM tablename t2 
        WHERE t1.ID = t2.ID AND t2.STAT = 0
      ) = 0

this query checks, is where a STAT with value 0 in records with same ID.

Answer 3

How about this:

select * from t where not (id in (select id from t where stat=0));

Answer 4

All 3 suggested solutions above work well, difference is only in the speed of execution. On a table where 3 records are found among approximately 7000 results are:

Martijn's: Showing rows 0 - 2 (3 total, Query took 0.0069 sec)

Alex's: Showing rows 0 - 2 (3 total, Query took 0.1162 sec)

Pavelasc's: Showing rows 0 - 2 (3 total, Query took 0.6160 sec)