Select counter (*) or zero

Question

Fatigue doesn't let me find this ... Say you have the following tables:

Parent

• PARENT_ID (LONG)

Child

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

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

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

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

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

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

twenty

parent_id = 5 >= 1 , . parent_id = 3. parent_id = 6 , , .

?

Answer 1

:

   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

, , .

Answer 2

, ?

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

, OMG Ponies ( +1), .

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

Answer 4

, , LONG, LONG WHERE . , 10g, , .

-, , , PARENT_ID, . , :

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

Answer 5

, Oracle :

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

, HAS_CHILDREN_WHO_GRADUATED 1 0 .

( where)