select the records only if ALL related records match

Question

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)

What does "not working" mean? Did you get an error, unexpected results, something else? Can you supply sample data and expected results? — Thom A, Mar 17 '20 at 12:16
Does this answer your question? [Select from one table matching criteria in another?](https://stackoverflow.com/questions/5446778/select-from-one-table-matching-criteria-in-another) — RamblinRose, Mar 17 '20 at 12:17

Gordon Linoff · Accepted Answer · 2020-03-17T13:47:40.450

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).

This will also select rows from table1 where the id is not related to an id of table2. — forpas, Mar 17 '20 at 12:29
@Henkie85 if you get rid of the join then the ids of the tables are not related. — forpas, Mar 17 '20 at 12:33

score 0 · Answer 2 · answered Mar 17 '20 at 12:17

0

You could drop the join to table2 and change the all to in or exists. or you could add t.status = 3 to the on clause and get rid of the where clause.

answered Mar 17 '20 at 12:17

Joe

147
1
8

forpas · Answer 3 · 2020-03-17T12:27:24.750

You can add NOT EXISTS to the ON clause:

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

This way you select from table1 all the rows that are related to table2 and the related rows all satisfy the condition that Status = 3.
Or, without a join, use in the WHERE clause a subquery that returns all the Ids of table2 that satisfy the condition Status = 3:

select * 
from table1  
where Id in (
  select Id from table2
  group by Id
  having min(Status) = 3 and max(Status) = 3
)

select the records only if ALL related records match

3 Answers3