Find All Parent Records Where All Child Records Have a Given Value (But Not Just Some Child Records)

find all parent records where all child records have a given value (but not just some child records)

If I get it right your problem can be classified as relational division. There are basically two ways to approach it:

1a) Forall x : p(x)

which in SQL has to be translated to:

1b) NOT Exists x : NOT p(x)

For your problem that would be something like:

SELECT e.* 
FROM events e
WHERE NOT EXISTS (
    SELECT 1 
    FROM PARTICIPANTS p
    WHERE p.status <> 'present'
      AND p.event_id = e.event_id
)

i.e. any given event where there does not exist a participant such that status != 'present'

The other principle way of doing it is to compare the number of participants with the number of participants with status present

SELECT e.id 
FROM events e
JOIN participants p 
    ON p.event_id = e.id 
GROUP BY e.event_id 
HAVING count(*) = count( CASE WHEN p.status = 'present' then 1 end )

Both solutions are untested so there might be errors in there, but it should give you a start

Find all parents where all child record matches exactly from given list in has many through association in Rails

The task can be solved easily with plain SQL. Assuming you use PostgreSQL 9+, this is SQL-query to fetch invoice ids which match your condition (have EXACTLY price matrixes with ids [1, 2] only):

SELECT I.id
FROM invoices I  
  JOIN entries E ON E.invoice_id = I.id
GROUP BY I.id
HAVING array_agg(E.price_matrix_id ORDER BY E.price_matrix_id ASC) = ARRAY[1,2];

Here we join invoices and entries, group results by invoice.id, and filter only those ones which have given price_matrix_ids. Please note that ARRAY[1,2] expression should contain price_matrix_ids sorted in ascending order.

Online demo: http://rextester.com/TAEUU9220

Back to Ruby, here is the code:

price_matrix_ids = [1,2].sort

Invoice.joins(:entries).having("array_agg(entries.price_matrix_id ORDER BY entries.price_matrix_id ASC) = ARRAY[#{price_matrix_ids.join(',')}]").group('invoices.id')

Mysql find parent records where given values are found in all child records

You can use HAVING:

SELECT *, count(option_name) AS total FROM Parent P
 LEFT JOIN Relations R ON R.parent_id = P.parent_id
 WHERE (option_name = Colors 
 AND option_value = aaa)
 OR (option_name = Country
 AND option_value = ddd)
 HAVING total > 1

You change the HAVING condition to find the number of options you are looking for

What is the query to get parent records against specific child or get child records against parent?

If I understand your question correctly that you don't want to insert null values in Parent_ID column then you should replace NULL with 0 and your updated code will be like:

;WITH DATA AS (
                SELECT p.PERSON_ID,p.Name, p.PARENT_ID
                FROM hierarchy p
                WHERE p.PERSON_ID = 9
                UNION ALL
                SELECT c.PERSON_ID,c.Name, c.PARENT_ID
                FROM hierarchy c
                JOIN DATA h 
                ON c.PERSON_ID = h.PARENT_ID 
          )
select * from DATA;

Need SQL Query to find Parent records without child records

You can use a NOT EXISTS clause for this

SELECT ParentTable.ParentID
FROM ParentTable
WHERE NOT EXISTS (
    SELECT 1 FROM ChildTable
    WHERE ChildTable.ParentID = ParentTable.ParentID
)

There's also the old left join and check for null approach

SELECT ParentTable.ParentID
FROM ParentTable
LEFT JOIN ChildTable
  ON ParentTable.ParentID = ChildTable.ParentID
WHERE ChildTable.ChildID IS NULL

Try both and see which one works better for you.

How to fetch all related parent and child records in a table?

Here is one way to do this. You need two hierarchical queries one which start with 33 and traverses up till parent is null and another one which start with 33 and moves down till the leaf.

with recursive cte
  as (select id,name,parent,cast(id as varchar(50)) as concat_val,id as root,cast(1 as int)  as lvl
        from t
       where id=33
       union all
       select a.id,a.name,a.parent,cast(concat(a.id,'/',b.concat_val) as varchar(50)),b.root,cast(b.lvl+1 as int)
         from t a
         join cte b
           on b.parent=a.id
        )
  ,cte2
  as (select id,name,parent,cast(id as varchar(50)) as concat_val,id as root,cast(1 as int)  as lvl
        from t
       where id=33
       union all
       select a.id,a.name,a.parent,cast(concat(a.id,'/',b.concat_val) as varchar(50)),b.root,cast(b.lvl-1 as int)
         from t a
         join cte2 b
           on b.id=a.parent
        ) 
select distinct * from (        
select * 
  from cte
union all  
select * 
  from cte2
)x
order by lvl desc

+----+------+--------+------------+------+-----+
| id | name | parent | concat_val | root | lvl |
+----+------+--------+------------+------+-----+
|  1 | ab   | null   | 1/21/33    |   33 |   3 |
| 21 | aa   | 1      | 21/33      |   33 |   2 |
| 33 | dd   | 21     | 33         |   33 |   1 |
| 77 | cc   | 33     | 77/33      |   33 |   0 |
+----+------+--------+------------+------+-----+

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=963f0522a3dd3d3d9f945e56ce746203

---

Related Topics