MySQL - Select rows where column value is only 0, group by another column?

(I edited this to demonstrate that it works and to clarify some things):

What you're probably interested in is the HAVING statement.

You can use a HAVING statement after your GROUP BY. Here is a pretty good explanation: https://www.dofactory.com/sql/having

In short, what the HAVING statement does is let you filter on your data in that returns as a result of your GROUP BY expression.

Try this:

SELECT 
     foreign_id, MAX(status) AS status
FROM
     myTable
GROUP BY 
     foreign_id
HAVING 
     MAX(status) = 0

Here is an example of it working (tested in a basic MS Access file):

Then I entered this code:

And I got these results:

Let me know if you have any questions!

Using GROUP_CONCAT and DISTINCT clause, we group all the unique statuses against a foreign_id. Now inside the HAVING clause, we just need to filter out only those foreign_id, where unique statuses is '0' only.

Try the following:

SELECT ID, foreign_id, status, GROUP_CONCAT(DISTINCT status) as statuses 
FROM table 
GROUP BY foreign_id 
HAVING statuses = '0'

You can use group by and having:

select foreign_id, max(status) as status
from t
group by foreign_id
having max(status) = 0;

Basically, you need to do the test after the group by.

If you want the complete rows, use not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.foreign_id = t.foreign_id and t2.status <> 0
                 );

As others have said, you should be aggregating the data, but there are lots of potential issues with this. But before we get to that, you have stated that you want the original records in the output - not the consolidated data - the simplest solution is to use 2 queries - one to identify the foreign_ids with only status=0 and one to get the corresponding rows

SELECT r.*
FROM examples r
JOIN ( SELECT s.foreign_id, MAX(s.status)
  FROM examples s
  GROUP BY s.foreign_id
  HAVING MAX(s.status)=0
) t
ON r.foreign_id=t.foreign_id

Alternatively you could get the foreign_ids where status!=0 and exclude those in the second query - which might be more efficient depending on the distribution of your data.

Where this comes unstuck is in the case where "status" < 0. The output will include these rows (potentially the same can happen with SUM()). There are several ways to address this, you could SUM(ABS(status)) or SUM(IF(status=0,0,1)) for example. As Madhur suggests you could also GROUP_CONCAT(status) and check that the output of the column is only '0'.

But I suspect you are about to tell me that status cannot be negative (or null?) as it is a nominal number. If that's the case then you are using the wrong data type - you should be using an ENUM. If that is the case, then you can't apply SUM() or MAX() to an ENUM but you can still use the SUM(IF(status=?,0,1)) or GROUP_CONCAT() methods.

Hence....

SELECT r.*
FROM examples r
JOIN ( SELECT s.foreign_id, SUM(IF(status=0,0,1))
  FROM examples s
  GROUP BY s.foreign_id
  HAVING SUM(IF(status=0,0,1))=0
) t
ON r.foreign_id=t.foreign_id

use aggregate function

select * from t where foreign_id in (
     select foreign_id
        from t
        group by foreign_id
        having min(status) = 0
       ) and status!=1