Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2011
    Posts
    27

    Unanswered: Help need in using same table having Parent and child relationship

    Hi,

    Below is my sample data of my table named "Groups"
    Code:
    with Groups as (
    select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
    select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
    select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
    select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
    select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all
    select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all
    select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all
    select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all
    select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all
    select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup )
    
    select * from groups;
    Expected result:
    Code:
    with ExpectedResult as (
    select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all
    select '' as GroupName,'peoplesoft' as SubGroup union all
    select '' as GroupName,'mysql' as SubGroup union all
    select 'Microsoft' as GroupName,'Nokia' as SubGroup union all
    select '' as GroupName,'EShop' as SubGroup union all
    select 'IBM' as GroupName,'Meiosys' as SubGroup union all
    select '' as GroupName,'UrbanCode' as SubGroup  )
    
    select * from ExpectedResult;
    Please show me some sample query to how to achieve this parent-child has the same table.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here you go.
    Code:
    SELECT parent.groupname
         , child.groupname As subgroup
    FROM   groups As parent
     LEFT
      JOIN groups As child
        ON child.idparentgroup = parent.groupid
    WHERE  parent.idparentgroup = 0
    Do you understand this query?
    Ask any questions you might have.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2011
    Posts
    27
    Hi GVEE,

    Thank you for your reply and little bit tweak on your logic to achieve my exact output as follows.

    Code:
    with Groups as (
    select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
    select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
    select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
    select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
    select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all
    select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all
    select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all
    select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all
    select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all
    select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup )
    
    SELECT
    	 CASE 
    		WHEN ROW_NUMBER() OVER 
    		(PARTITION BY GP.GroupName
    		 ORDER BY GP.GroupName,GCH.GroupName) = 1 THEN GP.GroupName
    		ELSE ''
    	 END AS ParentGroup
    	,GCH.GroupName
    FROM Groups GCH
    INNER JOIN Groups GP
    ON GCH.IdParentGroup = GP.GroupId;
    Thanks

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'd strongly advise that you leave that particular piece of the puzzle to your presentation layer (e.g. report, webpage, etc).
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2011
    Posts
    27
    Thank you gvee. i agree.

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    You might want to learn ANSI/ISO Standard SQL and stop using a 1970's Sybase dialect. This how to write the CTEs you posted.


    WITH Groups (grp_id, grp_name, parent_grp_id)
    AS
    (SELECT X.grp_id, X.parent_grp_id
    FROM (VALUES
    ((1, 'Oracle', 0),
    (2, 'Microsoft', 0),
    (3, 'IBM', 0),
    (4, 'SunMicrosystem', 1),
    (5, 'peoplesoft', 1),
    (6, 'mysql', 1),
    (7, 'Nokia', 2),
    (8, 'EShop', 2),
    (9, 'Meiosys', 3),
    (10, 'UrbanCode', 3))) AS X(grp_id, grp_name, parent_grp_id))
    SELECT * FROM Groups;

    Likewise:

    WITH ExpectedResult (grp_name, sub_grp_name)
    AS
    (SELECT X.grp_id, X.sub_grp_id
    FROM (VALUES
    (('Oracle', 'SunMicrosystem'),
    ('', 'peoplesoft'),
    ('', 'mysql'),
    ('Microsoft', 'Nokia'),
    ('', 'EShop'),
    ('IBM', 'Meiosys'),
    ('', 'UrbanCode')))
    AS X (grp_id, sub_grp_id))

    SELECT * FROM ExpectedResult;

    Please show me some sample query to how to achieve this parent-child has the same table.
    Throw this out and use the nested sets model instead. What you have is called an adjacency list model. It is not normalized and it is a bitch to use, as you found out.

  7. #7
    Join Date
    Apr 2011
    Posts
    27
    Hi Celko,

    Thank you so much for your tip. Good learning for me today about how to use CTE well. Seems there is syntax error on both of the sample you provided. any clue to solve the syntax issue. i tried. still i am unable to do as it is new to me.

    Thanks.

  8. #8
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Gee, the code passed this parser (below). T-SQL dialect can require a ; in front to kludge around one of several flaws in the CTE syntax.

    Mimer SQL Developers - Mimer SQL-2003 Validator

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    An ISO extension to Celko's query, with a non-ISO (and horrible relational practice) tweak at the end just to excite him a bit!
    Code:
    WITH Groups (grp_id, grp_name, parent_grp_id)
    AS 
    (
    SELECT X.grp_id, X.grp_name, X.parent_grp_id
       FROM (VALUES
          ( 1, 'Oracle',         0)
    , 	  ( 2, 'Microsoft',      0)
    , 	  ( 3, 'IBM',            0)
    , 	  ( 4, 'SunMicrosystem', 1)
    , 	  ( 5, 'peoplesoft',     1)
    , 	  ( 6, 'mysql',          1)
    , 	  ( 7, 'Nokia',          2)
    , 	  ( 8, 'EShop',          2)
    , 	  ( 9, 'Meiosys',        3)
    , 	  (10, 'UrbanCode',      3)
       ) AS X(grp_id, grp_name, parent_grp_id)
    )
    ,
    Heirarchy(lvl, grp_id, grp_name, tree) AS
    (
    SELECT 0, Groups.grp_id, Groups.grp_name, Cast(Cast(Groups.grp_name AS CHAR(20)) AS VARCHAR(255))
       FROM groups
       WHERE  0 = parent_grp_id
    UNION ALL SELECT
       1 + lvl, Groups.grp_id, Groups.grp_name, Cast(Heirarchy.tree + Cast(Groups.grp_name AS CHAR(20)) AS VARCHAR(255))
       FROM Heirarchy
       JOIN Groups
          ON (Groups.parent_grp_id = Heirarchy.grp_id)
    )
    SELECT Space(5 * lvl) + grp_name
       FROM Heirarchy
       ORDER BY tree;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Apr 2011
    Posts
    27
    Hi Celko,

    Thank you for the explanation.

    Hi Pat,

    nice code and i already achieve the result as below,
    Code:
    SELECT
    	 CASE 
    		WHEN ROW_NUMBER() OVER 
    		(PARTITION BY GP.GroupName
    		 ORDER BY GP.GroupName,GCH.GroupName) = 1 THEN GP.GroupName
    		ELSE ''
    	 END AS ParentGroup
    	,GCH.GroupName
    FROM Groups GCH
    INNER JOIN Groups GP
    ON GCH.IdParentGroup = GP.GroupId;
    from this post i understand how to post the data with CTE.
    thanks everyone participated on this thread.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •