Board index » database » SQL Query for Top Down fetching of childs
sgnerd
 Registered User |
SQL Query for Top Down fetching of childs
2004-01-29 02:11:18 PM
This is a multi-part message in MIME format. Dear Friends, Postgres 7.3.4 on RH Linux 7.2. I need a query to get the Childs of a parent (Top down analysis). Need to list all the departments(Childs) of a parent organization. The table structure is CREATE TABLE organization ( entity_id int4, entity_name varchar(100), entity_type varchar(25), parent_entity_id int4, ) WITH OIDS; A parent can have n number of Childs. So I need to list all the childs for a parent. For example I query the Division , then it lists it Childs # select * from organization where parent_entity_id = 3; entity_id | entity_name | entity_type | parent_entity_id -----------+-------------+-----------------+------------------ 5 | HR | EngineeringTeam | 3 12 | PM | EngineeringTeam | 3 8 | Finance | Dept | 3 6 | Quality | Dept | 3 I need to drill down to the last level Engineering Team in this example. So I query entity_id 8 further, it gives me its Childs =# select * from organization where parent_entity_id = 8; entity_id | entity_name | entity_type | parent_entity_id -----------+-------------+-------------+------------------ 15 | Audit | Group | 8 16 | Mkt | Group | 8 (2 rows) Again, I need to query the entity_id 15 to get its child =# select * from organization where parent_entity_id = 15; entity_id | entity_name | entity_type | parent_entity_id -----------+-------------+-----------------+------------------ 17 | CA | EngineeringTeam | 15 18 | Comm | EngineeringTeam | 15 19 | EComm | EngineeringTeam | 15 (3 rows) I have used the following query, but not useful. CREATE OR REPLACE FUNCTION.fn_get_all_organization(int4) RETURNS SETOF organization AS 'DECLARE pi_entity_id ALIAS FOR $1; rec_result organization%ROWTYPE; rec_proc organization%ROWTYPE; v_patent_entity_id INT; BEGIN FOR rec_result IN SELECT entity_id, entity_name, entity_type, parent_entity_id FROM organization ben WHERE ben.parent_entity_id = pi_entity_id LOOP IF rec_result.entity_type = \'EngineeingTeam\' THEN RETURN NEXT rec_result; ELSE v_patent_entity_id := rec_result.entity_id; LOOP FOR rec_proc IN SELECT bse.entity_id, bse.entity_name, bse.entity_type, bse.parent_entity_id FROM organization bse WHERE bse.parent_entity_id= v_patent_entity_id LOOP IF rec_proc.entity_type = \'EngineeringTeam\' THEN RETURN NEXT rec_proc; ELSE v_patent_entity_id := rec_proc.entity_id; END IF; END LOOP; EXIT WHEN rec_proc.entity_type = \'EngineeringTeam\'; END LOOP; END IF; END LOOP; RETURN; END;' LANGUAGE 'plpgsql' VOLATILE; Anybody pls help me with this. I am first time writing these kind of function for TOP DOWN analysis. Please shed light. Regards Senthil Kumar S <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> <META content="MSHTML 6.00.2800.1276" name=GENERATOR> <STYLE></STYLE> </HEAD> <BODY style="COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=#ffffff> <DIV><FONT size=2>Dear Friends,</FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>Postgres 7.3.4 on RH Linux 7.2.</FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>I need a query to get the Childs of a parent (Top down analysis). Need to list all the departments(Childs) of a parent organization. The table structure is </FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>CREATE TABLE organization<BR>(<BR> entity_id int4,<BR> entity_name varchar(100),<BR> entity_type varchar(25),<BR> parent_entity_id int4,<BR>) WITH OIDS;</FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>A parent can have n number of Childs. So I need to list all the childs for a parent.</FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>For example I query the Division , then it lists it Childs</FONT></DIV> <DIV><FONT size=2># select * from organization where parent_entity_id = 3;<BR> entity_id | entity_name | entity_type | parent_entity_id<BR>-----------+-------------+-----------------+------------------<BR> 5 | HR | EngineeringTeam | 3<BR> 12 | PM | EngineeringTeam | 3<BR> 8 | Finance | Dept | 3<BR> 6 | Quality | Dept | 3</FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>I need to drill down to the last level Engineering Team in this example.</FONT></DIV> <DIV><FONT size=2>So I query entity_id 8 further, it gives me its Childs</FONT></DIV> <DIV><FONT size=2>=# select * from organization where parent_entity_id = 8;<BR> entity_id | entity_name | entity_type | parent_entity_id<BR>-----------+-------------+-------------+------------------<BR> 15 | Audit | Group | 8<BR> 16 | Mkt | Group | 8<BR>(2 rows)</FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>Again, I need to query the entity_id 15 to get its child</FONT></DIV> <DIV><FONT size=2>=# select * from organization where parent_entity_id = 15;<BR> entity_id | entity_name | entity_type | parent_entity_id<BR>-----------+-------------+-----------------+------------------<BR> 17 | CA | EngineeringTeam | 15<BR> 18 | Comm | EngineeringTeam | 15<BR> 19 | EComm | EngineeringTeam | 15<BR>(3 rows)</FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>I have used the following query, but not useful.</FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2></FONT> </DIV> <DIV> <DIV><FONT size=2>CREATE OR REPLACE FUNCTION.fn_get_all_organization(int4)<BR> RETURNS SETOF organization AS<BR>'DECLARE<BR> pi_entity_id ALIAS FOR $1;<BR> rec_result organization%ROWTYPE;<BR> rec_proc organization%ROWTYPE;<BR> v_patent_entity_id INT;</FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>BEGIN<BR> FOR rec_result IN SELECT entity_id, entity_name, entity_type, parent_entity_id FROM organization ben<BR> WHERE ben.parent_entity_id = pi_entity_id</FONT></DIV> <DIV><FONT size=2> LOOP<BR> IF rec_result.entity_type = \'EngineeingTeam\' THEN<BR> RETURN NEXT rec_result;<BR> ELSE<BR> v_patent_entity_id := rec_result.entity_id; <BR> LOOP<BR> FOR rec_proc IN SELECT bse.entity_id, bse.entity_name, bse.entity_type, bse.parent_entity_id FROM organization bse<BR> WHERE bse.parent_entity_id= v_patent_entity_id</FONT></DIV> <DIV><FONT size=2> LOOP<BR> IF rec_proc.entity_type = \'EngineeringTeam\' THEN<BR> RETURN NEXT rec_proc;<BR> ELSE<BR> v_patent_entity_id := rec_proc.entity_id;<BR> END IF;<BR> END LOOP;<BR> EXIT WHEN rec_proc.entity_type = \'EngineeringTeam\';<BR> END LOOP;<BR> END IF; <BR> END LOOP;<BR>RETURN;<BR>END;'<BR> LANGUAGE 'plpgsql' VOLATILE;<BR></FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>Anybody pls help me with this. I am first time writing these kind of function for TOP DOWN analysis. Please shed light.</FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>Regards</FONT></DIV> <DIV><FONT size=2>Senthil Kumar S</FONT></DIV> <DIV><FONT size=2> </DIV></FONT></DIV> <P></P></BODY></HTML> - |