Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2012
    Posts
    1

    Unanswered: Need to get all children of a parent if present or parent itself

    Hi,

    I have the below query which in turn goes in the where clause

    Code:
    SELECT CASE WHEN EXISTS (SELECT child.id FROM InspectionType parent,  
                                  InspectionType child WHERE parent.id = child.parentId AND 
                                   parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) 
    THEN (SELECT child.id FROM InspectionType parent, InspectionType child  
              WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)  
    ELSE (SELECT id FROM InspectionType WHERE  code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129)  
    END
    I am getting below error

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    I tried to solve this problem with If and else but it is not going well with in-clause

    Code:
    select * from InspectionType where id in (select IF EXISTS (SELECT child.id FROM InspectionType parent, 
    						InspectionType child WHERE parent.id = child.parentId AND
    						 parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
    
    BEGIN (SELECT child.id FROM InspectionType parent, InspectionType child 
    		WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) 
    END
    ELSE (SELECT id FROM InspectionType WHERE  code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129) )

    I am using select * from InspectionType where id in() for illustation only. The sub-query results will go another query

    Code:
    SELECT child.id FROM InspectionType parent, InspectionType child 
    		WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129
    
    This returns more than one row
    I know why is the error message but need to achieve this functionality. Please help with any other ideas

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your logic reduces to this:
    Code:
    SELECT	coalesce(child.id, parent.id) as id
    FROM	InspectionType parent
    	left outer join join InspectionType child on parent.id = child.parentId
    WHERE	parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' 
    	AND parent.zoneId = 10129
    ...but this still leaves you with the problem of what to do when there is more than one child ID with the same parent ID. Do you have a way of prioritizing them?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •