select the records only if ALL related records match

Again I am stuck with a query. I want to select all records from table1 in which ALL related table2 records satisfy a condition. Only when the ALL table 2 records satisfy it.

I tried this. But it’s not working.

select m.* from table1 m
 inner join table2 I on m.Id = I.Id
where 
I.Id = ALL (select t.Id from table2 t where t.Id = I.Id and t.Status = 3)

Answer

If you want the rows from m where all statuses are 3 in I, then use not exists:

select m.* 
from table1 m
where not exists (select 1
                  from table2 I 
                  where I.Id = m.Id and I.status <> 3
                 );

EDIT:

Note that this matches rows where there are no matches in table2. That technically meets the requirement that all rows have a status of 3. But if you want to require a row, you can add an exists condition:

select m.* 
from table1 m
where not exists (select 1
                  from table2 I 
                  where I.Id = m.Id and I.status <> 3
                 ) and
      exists (select 1
              from table2 I 
              where I.Id = m.Id and I.status = 3
             );

Both of these can take advantage of an index on table2(Id, status). Methods that use some form of aggregation require additional work and should be a little less performant on large data sets (particularly when there are many matches in table2).