troubleshooting Question

get child records corresponding to parent inclding the record if parent itself is child to someone from the same table

Avatar of Rocking
Rocking asked on
Oracle DatabaseMicrosoft SQL Server 2008
7 Comments1 Solution1479 ViewsLast Modified:
table name file_relation_parent_child

id  parent_file_id child_file_id
1      A            B
2      A            C
3      B            D
4      C            E
5      F            K
6      A            J
7      P            A  

hi,

i need to create a query which will return me the all the child_file_id of the parent_file_id  which is having the refrence including the parent of itself?


if the parent file is refrencing the child id and child is further parent of another file then it is denoted by **
if the parent file is refrencing the child id and child is not further parent of another file then it is denoted by *
if the file searched for is itself child of another file ( A is child of P) then it is denoted by ##

Output
A -> **B
A->  **C
A -> *J
A -> ##P

'
but the last case (if the file searched for is itself child of another file ( A is child of P) then it is denoted by ##) is not coming in the result,how to modify below query

  SELECT    r1.parent_file_id
         || '->'
         || r1.child_file_id
         || CASE WHEN r2.id IS NOT NULL THEN '**' ELSE '*' END
    FROM file_relation_parent_child r1
         LEFT OUTER JOIN file_relation_parent_child r2 ON r1.child_file_id = r2.parent_file_id
   WHERE r1.parent_file_id = 'A'
ORDER BY r1.parent_file_id, r1.child_file_id
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros