Select count(*) or zero

Question

Hello,

Tiredness prevents me from finding this one... Say you have the following tables:

Parent

• PARENT_ID (LONG)

Child

• CHILD_ID (LONG)
• PARENT_ID (LONG, FK)
• HAS_GRADUATED (BOOLEAN)

I want a query to return the following true (1, in the case of Oracle) if the parent has at least one child that has graduated, and false (0, in the case if Oracle) if the parent does not have a child that has graduated, or has no children at all:

PARENT_ID................HAS_CHILDREN_WHO_GRADUATED

5.................................1

3.................................1

6.................................0

2.................................0

In the above, parent with parent_id=5 may have >=1 children that have graduated. Same is parent with parent_id=3. Parent with parent_id=6 either has no children at all, or has children but none of them has graduated.

What would the query to this be like?

Answer 1

First of all I don't think you can use LONG columns for this since LONG values cannot be used in WHERE conditions. Note this is true as of 10g, since that's what I use.

Second I assume you mean that your child table should have a column called PARENT_ID otherwise there would be no way to link the two tables. Given that, this query ought to work:

SELECT PARENT_ID, COUNT(1) FROM Child WHERE HAS_GRADUATED = 1 GROUP BY PARENT_ID

Answer 2

Will this give you what you expect?

SELECT 
    P.Parent_Id,
    CASE WHEN (SUM (CASE WHEN Has_Graduated = 1 then 1 else 0 END)) = 0 THEN 0 ELSE 1  as HAS_CHILDREN_WHO_GRADUATED
FROM Parent P
    LEFT JOIN Child C
        ON P.Parent_Id = C.Parent_Id
GROUP BY P.Parent_Id

Answer 3

Use:

   SELECT DISTINCT
          p.parent_id,
          CASE WHEN c.parent_id IS NULL THEN 0 ELSE 1 END
     FROM PARENT p
LEFT JOIN CHILD c ON c.parent_id = p.parent_id
                 AND c.has_graduated = 1

You have to use an outer join in order to see the parent values that don't have supporting records in the child table.

Answer 4

Here's the form of the query, though the syntax for Oracle may be off:

SELECT
   Parent.PARENT_ID
  ,case count(Child.PARENT_ID) when 0 then 0 else 1 end HAS_CHILDREN_WHO_GRADUATED
 from Parent
  left outer join Child
   on Child.PARENT_ID = Parent.PARENT_ID
 where Child.HAS_GRADUATED = 1
 group by Parent.PARENT_ID

This will list all Parent items once, with HAS_CHILDREN_WHO_GRADUATED set to 1 or 0 set as desired.

(Edited to add the where clause)

Answer 5

It is likely that OMG Ponies solution will perform better (which is why he got my +1), but this yet another way of solving the problem.

Select Parent_Id
    , Case
        When Exists( Select 1
                    From Child
                    Where Child.Parent_Id = Parent.Parent_Id
                        And Child.Has_Graduated = 1 ) Then 1
        Else 0
        End
From Parent