Select Rows Where Every Child Row Meets a Condition

Select rows where every child row meets a condition

Using sub-query for counts

SQL> WITH manager(Manager_ID, Name, Department) AS (
  2    SELECT 1,  'Steve', 'Sales' FROM dual UNION ALL
  3    SELECT 2, 'Ben', 'Sales' FROM dual UNION ALL
  4    SELECT 3, 'Molly', 'Accounts' FROM dual),
  5  employee(Employee_ID, Manager_ID, Name, Age) AS (
  6    SELECT 1 , 1, 'Kyle', 25 FROM dual UNION ALL
  7    SELECT 2 ,1, 'Gary', 31  FROM dual UNION ALL
  8    SELECT  3, 2, 'Renee', 31  FROM dual UNION ALL
  9    SELECT 4, 2 , 'Oliver', 32  FROM dual)
 10  ---------------------------
 11  --- End of data preparation
 12  ---------------------------
 13  SELECT m.name
 14    FROM manager m
 15    JOIN (SELECT manager_id,
 16                 COUNT(1) total,
 17                 COUNT(CASE WHEN age > 30 THEN 1 ELSE NULL END) age_30_above
 18            FROM employee
 19           GROUP BY manager_id) ee
 20      ON m.manager_id = ee.manager_id
 21   WHERE total = age_30_above;

Output

NAME
-----
Ben

Your query will be:

SELECT m.name
  FROM manager m
  JOIN (SELECT manager_id,
               COUNT(1) total,
               COUNT(CASE WHEN age > 30 THEN 1 ELSE NULL END) age_30_above
          FROM employee
         GROUP BY manager_id) ee
    ON m.manager_id = ee.manager_id
 WHERE total = age_30_above;

Select only parent records when all children records meet conditions

Alternatively:

select a.*
from table_a a
where a.level = 'low' and
      'close' = all (select phase
                  from table_b b
                  where b.parent_primary_key = a.primary_key
                 );

SQL get parent row if child rows match multiple conditions

There are some other possibilities (subqueries in WHERE clause, for example) but the one You provided as (0) is most elegant and semantically correct in my opinion.

As for performance, You can try to tune it when the need arises, not before because "premature optimization is the root of all evil". ;)

Get parent rows where multiple child rows meet different conditions

I might start by writing this using exists:

SELECT i.*
FROM T_INDEX i
WHERE EXISTS (SELECT 1
              FROM T_INDEXCALLER ic
              WHERE ic.Name = @name AND i.IndexId = ic.IndexId
             ) AND
      EXISTS (SELECT 1
              FROM T_INDEXCALLER ic
              WHERE ic.ExtensionNumber = @extension AND i.IndexId = ic.IndexId
             );

This can take advantage of an index T_INDEXCALLER(indexId, Name, ExtensionNmber).

This may provide some speed up. An alternative would be:

SELECT i.*
FROM T_INDEX i JOIN
     (SELECT ic.indexId
      FROM ((SELECT ic.indexId, 1 as priority
             FROM T_INDEXCALLER
             WHERE ic.ExtensionNumber = @extension
            ) UNION ALL
            (SELECT ic.indexId, 2 as priority
             FROM T_INDEXCALLER
             WHERE ic.Name = @name
            )
           ) ic
      GROUP BY ic.indexId
      HAVING MIN(priority) = 1 AND MAX(priority) = 2
     ) ic
     ON ic.indexId = i.indexId;

This can take advantage of indexes on T_INDEXCALLER(name, indexId), T_INDEXCALLER(ExtensionNumber, indexId), and (very importantly) T_INDEX(indexId).

Select rows in a self-relationship where all children meet a condition

SELECT DISTINCT `documents`.*
FROM (`documents`)
LEFT OUTER JOIN `documents` children_documents
  ON `documents`.`id` = `children_documents`.`parent_id`
  AND `children_documents`.`status` = 'accepted'
WHERE `children_documents`.`parent_id` IS NULL

Select rows where all children contain same value only and no other scenario

Assuming your data structure is:

parentid   childid   value

Then you can do what you want with aggregation:

select parentid
from t
group by parentid
having min(value) = max(value) and  -- all values are the  same
       min(value) = 5               -- and that value is 5

If value can be NULL and you want to avoid that, also include:

 count(value) = count(*)

Select No Rows If Any Row Meets A Condition?

Something like this ?

SELECT * 
FROM SOMETABLE 
WHERE ID = 1 
AND NOT EXISTS (SELECT 1 FROM SOMETABLE WHERE INDICATOR = 'X')

or, if you want the X to discriminate only on the same id:

SELECT * 
FROM SOMETABLE t1
WHERE t1.ID = 1 
AND NOT EXISTS (SELECT 1 FROM SOMETABLE t2 WHERE t1.ID = t1.ID AND INDICATOR = 'X')

---

Related Topics