Home » SQL & PL/SQL » SQL & PL/SQL » SQL for Parent-Child Hierarchy
SQL for Parent-Child Hierarchy [message #573025] Thu, 20 December 2012 01:17 Go to next message
sanushks
Messages: 8
Registered: December 2012
Location: India
Junior Member
Please suggest which would be best way to achieve the below logic.

SQL to pick up parent child relationship within same table with a certain logic.

Example:
mod_product_number Product_Hierarchy
H555888 PH05678
H888987 H555888
H8889 H555888
H9955 H555888
H999999 H555888
P6666 H999999
P5555 H999999

Example: I expect the rows with H8889,H9955 & P6666 & P5555 to be sub-category values value for product hierarchy H555888.
If there are rows with H8888987 as Product_hierarchy, we will pull up those rows too for product hierarchy H555888.
The extra condition is we drill down only on 7 character mod_prod_number not on 5 character mod_prod_number. We pull out all sub category mod_prod_number for all distinct Product hierarchy.


Re: SQL for Parent-Child Hierarchy [message #573035 is a reply to message #573025] Thu, 20 December 2012 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: SQL for Parent-Child Hierarchy [message #573059 is a reply to message #573025] Thu, 20 December 2012 07:07 Go to previous messageGo to next message
userora
Messages: 55
Registered: December 2012
Location: Chennai
Member
Hi,

i am new to the forum.but as per your requirement i have tried using Hirearchy method..Please check if this is the result you need.

[Updated on: Thu, 20 December 2012 07:12] by Moderator

Report message to a moderator

Re: SQL for Parent-Child Hierarchy [message #573060 is a reply to message #573059] Thu, 20 December 2012 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i am new to the forum.but as per your requirement i ...


You didn't satisfy ANY point of my requirements.
In addition, do NOT post image for text, put it as text inline, no attachment.

Regards
Michel
Re: SQL for Parent-Child Hierarchy [message #573189 is a reply to message #573060] Sun, 23 December 2012 21:25 Go to previous messageGo to next message
sanushks
Messages: 8
Registered: December 2012
Location: India
Junior Member
Hi Michel,

I do not have the SQL yet to post the code. The hierarchy model does not seem to help solve my problem .Below is my requirement:

I would like to use the Product Hierarchy table to drill through each Product_part_table and get below output:

Input table:

Product_Part_table
--------------------

mod_product_number | Product_Hierarchy
H555888 | PH05678
H8889 | H555888
H888987 | H555888
H9955 | H555888
H999999 | H555888
P5555 | H999999
P6666 | H999999
H999999 | PH05678
H8888 | PH999999
H7777 | H999999

Product Hierarchy Table(Input table):

Product_Hierarchy
PH05678
PH08968


Output Table
Product Hierarchy | mod_product_num
PH05678 | H8889
PH05678 | H9955
PH05678 | P5555
PH05678 | P6666
PH05678 | H8888
PH05678 | H7777
PH08968 | H8888
PH08968 | H7777
PH08968 | P5555
PH08968 | P6666



Re: SQL for Parent-Child Hierarchy [message #573190 is a reply to message #573189] Sun, 23 December 2012 21:41 Go to previous messageGo to next message
BlackSwan
Messages: 22525
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

If you don't follow Posting Guidelines, then you don't get any answers.
Re: SQL for Parent-Child Hierarchy [message #573200 is a reply to message #573189] Mon, 24 December 2012 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I do not have the SQL yet to post the code.


You just need your fingers to type the CREATE TABLE and INSERT statements for the example you posted.

And FORMAT your post.

Regards
Michel
Re: SQL for Parent-Child Hierarchy [message #573215 is a reply to message #573200] Mon, 24 December 2012 04:39 Go to previous messageGo to next message
userora
Messages: 55
Registered: December 2012
Location: Chennai
Member
Hi sanushks,

I have tried this output.Hope this Query may satisfy your resultset..
==================================================================================================
CREATE TABLE PRODUCT_PART_TABLE (MOD_PRODUCT_NUMBER VARCHAR2(50), PRODUCT_HIERARCHY VARCHAR2(50));

INSERT INTO PRODUCT_PART_TABLE VALUES ('H555888' , 'PH05678');
INSERT INTO PRODUCT_PART_TABLE VALUES ('H8889' , 'H555888');
INSERT INTO PRODUCT_PART_TABLE VALUES ('H9955' , 'H555888');
INSERT INTO PRODUCT_PART_TABLE VALUES ('H999999' , 'H555888'); 
INSERT INTO PRODUCT_PART_TABLE VALUES ('P5555' , 'H999999');
INSERT INTO PRODUCT_PART_TABLE VALUES ('P6666' , 'H999999'); 
INSERT INTO PRODUCT_PART_TABLE VALUES ('H999999' , 'PH08968'); 
INSERT INTO PRODUCT_PART_TABLE VALUES ('H8888' , 'H999999');
INSERT INTO PRODUCT_PART_TABLE VALUES ('H7777' , 'H999999'); 

SELECT MOD_PRODUCT_NUMBER,PRODUCT_HIERARCHY
  FROM PRODUCT_PART_TABLE 
GROUP BY MOD_PRODUCT_NUMBER,PRODUCT_HIERARCHY

MOD_PRODUCT_NUMBER	PRODUCT_HIERARCHY
H9955	                H555888
H8889	                H555888
H555888	                PH05678
P6666	                H999999
H8888	                H999999
P5555	                H999999
H7777	                H999999
H999999	                H555888
H999999	                PH08968

====================================================================================================
CREATE TABLE PRODUCT_HIERARCHY (PROD_HIER VARCHAR2(50))

INSERT INTO PRODUCT_HIERARCHY VALUES ('PH05678');
INSERT INTO PRODUCT_HIERARCHY VALUES ('PH08968');

SELECT * 
  FROM PRODUCT_HIERARCHY

PROD_HIER
PH08968
PH05678

=====================================================================================================
CREATE TABLE OUTPUT_HIER (PRODUCT_HIERARCHY VARCHAR2(50) , MOD_PRODUCT_NUM VARCHAR2(50));

SELECT * 
  FROM OUTPUT_HIER 

PROD_HIER	MOD_PRODUCT_NUMBER
----------------------------------
Parent-Child-Relationship
=========================
Output Table Result must be like this am i right

SELECT * FROM OUTPUT_HIER 

==================================
Product Hierarchy | mod_product_num
============================================= 
PH05678 | H555888 | H8889 
PH05678 | H555888 | H9955 
PH05678 | H555888 | H999999 | P5555 
PH05678 | H555888 | H999999 | P6666 
PH05678 | H555888 | H999999 | H8888 
PH05678 | H555888 | H999999 | H7777 
PH08968 | H999999 | H8888 
PH08968 | H999999 | H7777 
PH08968 | H999999 | P5555 
PH08968 | H999999 | P6666 
PH08968 | H999999 | H555888 | H8889 
PH08968 | H999999 | H555888 | H9955
=======================================================================================================
INSERT INTO OUTPUT_HIER
(SELECT PROD_HIER , MOD_PRODUCT_NUMBER 
  FROM PRODUCT_PART_TABLE A,PRODUCT_HIERARCHY B
 WHERE PROD_HIER NOT IN (
                         SELECT  PRODUCT_HIERARCHY 
                           FROM PRODUCT_PART_TABLE 
                          WHERE MOD_PRODUCT_NUMBER NOT IN (
                                                           SELECT A.MOD_PRODUCT_NUMBER 
                                                             FROM PRODUCT_PART_TABLE A,PRODUCT_HIERARCHY B
                                                            WHERE A.PRODUCT_HIERARCHY = B.PROD_HIER 
                                                           )
                        )
  AND MOD_PRODUCT_NUMBER NOT IN (
                                 SELECT A.MOD_PRODUCT_NUMBER 
                                   FROM PRODUCT_PART_TABLE A,PRODUCT_HIERARCHY B
                                  WHERE A.PRODUCT_HIERARCHY = B.PROD_HIER
                                )
)     
===============================================
PROD_HIER	MOD_PRODUCT_NUMBER
PH08968	        H8889
PH08968	        P5555
PH08968	        H9955
PH08968	        P6666
PH08968	        H8888
PH08968	        H7777
PH05678	        H8889
PH05678	        P5555
PH05678	        H9955
PH05678	        P6666
PH05678	        H8888
PH05678	        H7777           
==========================================================================================================   


[EDITED by LF: applied [code] tags]

[Updated on: Mon, 24 December 2012 15:44] by Moderator

Report message to a moderator

Re: SQL for Parent-Child Hierarchy [message #573217 is a reply to message #573215] Mon, 24 December 2012 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't read the link, did you?

Regards
Michel
Re: SQL for Parent-Child Hierarchy [message #573243 is a reply to message #573217] Mon, 24 December 2012 15:44 Go to previous messageGo to next message
Littlefoot
Messages: 19327
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel (apart from the fact that someone didn't read the link), these are two different persons. The last message was actually an answer, not yet another attempt of a question.
Re: SQL for Parent-Child Hierarchy [message #573244 is a reply to message #573243] Mon, 24 December 2012 20:15 Go to previous messageGo to next message
sanushks
Messages: 8
Registered: December 2012
Location: India
Junior Member
Thanks vnithya.. Appreciate the effort!
One part i see issue is you seem to be drilling down on the Product Hierarchy of the Product_Part_Table, i would need the logic to drill down only on the mod_prod_number which is 7 character in length, so i would not get the below rows in the final output
PH05678 | H8889
PH05678 | H9955

Just want to check with you, will this week for a child any level deep? If the child is 5 levels deep, will the query still work & produce the desired output?
Re: SQL for Parent-Child Hierarchy [message #573259 is a reply to message #573244] Tue, 25 December 2012 23:33 Go to previous message
userora
Messages: 55
Registered: December 2012
Location: Chennai
Member
Hi sanushks

Thanks Smile,I will try this query to drill down the mod_prod_number which is of 7 character in length.

Actually the Query which i gave will give the result set for a child at any level deepth.
For example If the child is 5 levels deep, this query still work & produce the desired output.
Previous Topic: Select query like operator getting wrong data
Next Topic: date query issue
Goto Forum:
  


Current Time: Tue Jul 29 15:07:11 CDT 2014

Total time taken to generate the page: 0.11882 seconds