Avatar of Rocking
Rocking
 asked on

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

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
Microsoft SQL Server 2008Oracle Database

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
Sean Stuber

oracle

SELECT    r1.parent_file_id
       || '->'
       || CASE WHEN r2.id IS NOT NULL THEN '**' ELSE '*' END
       || r1.child_file_id
  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'
UNION ALL
SELECT r1.child_file_id || '->##' || r1.parent_file_id
  FROM file_relation_parent_child r1
       JOIN file_relation_parent_child r2 ON r1.child_file_id = r2.parent_file_id
 WHERE r1.child_file_id = 'A' AND ROWNUM = 1


sql server

SELECT    r1.parent_file_id
       + '->'
       + CASE WHEN r2.id IS NOT NULL THEN '**' ELSE '*' END
       + r1.child_file_id
  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'
UNION ALL
SELECT TOP 1r1.child_file_id + '->##' + r1.parent_file_id
  FROM file_relation_parent_child r1
       JOIN file_relation_parent_child r2 ON r1.child_file_id = r2.parent_file_id
 WHERE r1.child_file_id = 'A'
Sean Stuber

another option

oracle

SELECT DISTINCT
          r1.parent_file_id
       || '->'
       || CASE WHEN r2.id IS NOT NULL THEN '**' ELSE '*' END
       || r1.child_file_id
  FROM file_relation_parent_child r1
       LEFT OUTER JOIN file_relation_parent_child r2 ON r1.child_file_id = r2.parent_file_id
 WHERE 'A' IN (r1.parent_file_id, r1.child_file_id);


sql server


SELECT DISTINCT
          r1.parent_file_id
       + '->'
       + CASE WHEN r2.id IS NOT NULL THEN '**' ELSE '*' END
       + r1.child_file_id
  FROM file_relation_parent_child r1
       LEFT OUTER JOIN file_relation_parent_child r2 ON r1.child_file_id = r2.parent_file_id
 WHERE 'A' IN (r1.parent_file_id, r1.child_file_id);
Rocking

ASKER
why join is used in second query instead of outer join?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Sean Stuber

oops good catch.  You don't even need the join in the second query
that was a bad cut-n-paste without cleanup on my part.  


SELECT    r1.parent_file_id
       || '->'
       || CASE WHEN r2.id IS NOT NULL THEN '**' ELSE '*' END
       || r1.child_file_id
  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'
UNION ALL
SELECT r1.child_file_id || '->##' || r1.parent_file_id
  FROM file_relation_parent_child r1      
 WHERE r1.child_file_id = 'A' AND ROWNUM = 1


SELECT    r1.parent_file_id
       + '->'
       + CASE WHEN r2.id IS NOT NULL THEN '**' ELSE '*' END
       + r1.child_file_id
  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'
UNION ALL
SELECT TOP 1r1.child_file_id + '->##' + r1.parent_file_id
  FROM file_relation_parent_child r1      
 WHERE r1.child_file_id = 'A'
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rocking

ASKER
i think union also returning the same result why used union all ?
I there any performance hit if we use union and union all ?
Sean Stuber

UNION forces sorting and comparing both sets to ensure the final results are distinct which is completely unnecessary since the two portions are guaranteed to be distinct.

UNION ALL skips those steps


For small data samples you won't see a difference but UNION ALL is more correct for this type of query
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.