Sql: Select Records Where All Joined Records Satisfy Some Condition

SQL: Select records where ALL joined records satisfy some condition

Assuming no need for correlation, use:

SELECT a.*
  FROM A a
 WHERE EXISTS(SELECT NULL
                FROM B b
              HAVING MIN(b.some_val) > a.val)

If you do need correlation:

SELECT a.*
  FROM A a
 WHERE EXISTS(SELECT NULL
                FROM B b
               WHERE b.id = a.id
              HAVING MIN(b.some_val) > a.val)

Explanation

The EXISTS evaluates on a boolean, based on the first match - this makes it faster than say using IN, and -- unlike using a JOIN -- will not duplicate rows. The SELECT portion doesn't matter - you can change it to EXISTS SELECT 1/0 ... and the query will still work though there's an obvious division by zero error.

The subquery within the EXISTS uses the aggregate function MIN to get the smallest B.some_val - if that value is larger than the a.val value, the a.val is smaller than all of the b values. The only need for a WHERE clause is for correlation - aggregate functions can only be used in the HAVING clause.

SQL Select where all related entries satisfy condition

A simple not exists would satisfy your criteria?

select a.name
from TableA a
where a.type='note'
and not exists (select * from TableB b where b.a_id=a.id and b.structure='successful')

Result: 'woof'

select the records only if ALL related records match

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

Select records where every record in one-to-many join matches a condition

I would suggest the following:

select distinct a.* 
from a inner join 
(
    select b.a_id 
    from b 
    group by b.a_id 
    having min(b.string) = max(b.string) and min(b.string) = 'string'
) c on a.id = c.a_id

Alternatively:

select distinct a.* 
from a inner join b on a.id = b.a_id
where not exists (select 1 from b c where c.a_id = a.id and c.string <> 'string')

Note: In the above examples, only change the symbols a and b to the names of your tables; the other identifiers are merely aliases and should not be changed.

Postgres select where all joined rows match

Since you are comfortable with arrays, please give this a try:

with inparms as (
  select array['A', 'B'] as inarray
), mappings as (
  select service_id, 
         array_agg(provision) as all_provs, 
         array_agg(provision) filter (where required) req_provs
    from service_provision
   group by service_id
)
select m.* 
  from mappings m
  cross join inparms i
 where i.inarray && all_provs
   and (req_provs is null or i.inarray @> req_provs);

 service_id | all_provs | req_provs 
------------+-----------+-----------
          2 | {B,C}     | {B}
          1 | {A}       | 
(2 rows)

inparms takes in your list of provision values.

mappings groups the entries into arrays to represent all and required provision values.

The comparison in the query's where clause looks for any overlap with && and then
sees if req_provs is null or if req_provs is entirely contained within inarray.

Select IDs from multiple rows where column values satisfy one condition but not another

SELECT FirstID
FROM table
WHERE SecondId in (1,2,3) --Included values
AND FirstID NOT IN (SELECT FirstID FROM test
                    WHERE SecondId IN (4,5)) --Excluded values

---

Related Topics