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
LEFT OUTER JOIN file_relation_parent_child
WHERE r1.parent_file_id = 'A'
ORDER BY r1.parent_file_id, r1.child_file_id
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
LEFT OUTER JOIN file_relation_parent_child
WHERE r1.parent_file_id = 'A'
ORDER BY r1.parent_file_id, r1.child_file_id
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
LEFT OUTER JOIN file_relation_parent_child
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
LEFT OUTER JOIN file_relation_parent_child
WHERE 'A' IN (r1.parent_file_id, r1.child_file_id);
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
LEFT OUTER JOIN file_relation_parent_child
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
LEFT OUTER JOIN file_relation_parent_child
WHERE 'A' IN (r1.parent_file_id, r1.child_file_id);
ASKER
why join is used in second query instead of outer join?
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
LEFT OUTER JOIN file_relation_parent_child
WHERE r1.parent_file_id = 'A'
UNION ALL
SELECT r1.child_file_id || '->##' || r1.parent_file_id
FROM file_relation_parent_child
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
LEFT OUTER JOIN file_relation_parent_child
WHERE r1.parent_file_id = 'A'
UNION ALL
SELECT TOP 1r1.child_file_id + '->##' + r1.parent_file_id
FROM file_relation_parent_child
WHERE r1.child_file_id = 'A'
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
LEFT OUTER JOIN file_relation_parent_child
WHERE r1.parent_file_id = 'A'
UNION ALL
SELECT r1.child_file_id || '->##' || r1.parent_file_id
FROM file_relation_parent_child
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
LEFT OUTER JOIN file_relation_parent_child
WHERE r1.parent_file_id = 'A'
UNION ALL
SELECT TOP 1r1.child_file_id + '->##' + r1.parent_file_id
FROM file_relation_parent_child
WHERE r1.child_file_id = 'A'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ?
I there any performance hit if we use union and union all ?
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
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
SELECT r1.parent_file_id
|| '->'
|| CASE WHEN r2.id IS NOT NULL THEN '**' ELSE '*' END
|| r1.child_file_id
FROM file_relation_parent_child
LEFT OUTER JOIN file_relation_parent_child
WHERE r1.parent_file_id = 'A'
UNION ALL
SELECT r1.child_file_id || '->##' || r1.parent_file_id
FROM file_relation_parent_child
JOIN file_relation_parent_child
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
LEFT OUTER JOIN file_relation_parent_child
WHERE r1.parent_file_id = 'A'
UNION ALL
SELECT TOP 1r1.child_file_id + '->##' + r1.parent_file_id
FROM file_relation_parent_child
JOIN file_relation_parent_child
WHERE r1.child_file_id = 'A'