Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: Hierarchy sql query

    Hi all,

    I have a table Category whose structure is as follows:

    id
    name
    parentid

    So i have number of nodes from the root of a specific category. Any advice in how to get all the sub categories(ie nodes) for all parent categories? Thanks.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You need recursive SQL for that:
    Code:
    CREATE table #Category(
    	id		INT	NOT NULL,
    	name	VARCHAR(20)	NOT NULL,
    	parentid	INT	NOT NULL
    )
    
    INSERT INTO #Category (id, name, parentid) VALUES
    (1, 'Highest level', 1),
    (2, 'Second level 1', 1),
    (3, 'Second level 2', 1),
    (4, 'Thirth level 1/1', 2),
    (5, 'Thirth level 1/2', 2),
    (6, 'Thirth level 2/1', 3),
    (7, 'Fourth level 2/1/1', 6),
    (8, 'Fifth level 2/1/1/1', 7)
    
    DECLARE @TopLevelId	INT;
    SET @TopLevelId = 3;
    
    WITH CTE AS
    (SELECT id, name, parentid, id as Adam, 1 as level
    FROM #Category
    WHERE id = @TopLevelId
    	UNION ALL
    SELECT #Category.id, #Category.name, #Category.parentid, CTE.Adam, CTE.level + 1
    FROM #Category 
    	INNER JOIN CTE ON
    		#Category.parentid = CTE.id
    WHERE #Category.id <> @TopLevelId
    )
    SELECT * 
    FROM CTE
    ORDER BY level, id
    Adam is the top most parentId (in other words: @TopLevelId)
    Last edited by Wim; 01-18-12 at 06:00. Reason: added test table
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    Hi Wim,

    Thanks for prompt reply. Solution you gave works perfectly fine although am still staggering a bit with grasping recursive sql but it works. Will get back to thread if am stuck on something but many thanks once again for your precious help.

  4. #4
    Join Date
    Jul 2009
    Posts
    168
    Sorry wim, back to annoy you a bit I am displaying products category on a web page so this is why am querying the Category table to get all the parent-child nodes. The query you specified works great but how do I get the children right after the parent node so that I do not have to make a search though an array programatically, if you see what i mean. I can post out my table data example if you need it. thanks. regards.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by kpeeroo View Post
    The query you specified works great but how do I get the children right after the parent node so that I do not have to make a search though an array programatically, if you see what i mean.
    Not at all. The query delivers the starting parent and all its descendants in the Id column. Give some example data and the result you want.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jul 2009
    Posts
    168
    Hi Wim, sorry for late reply. Here goes:

    Category
    ---------

    id-----name------parentid
    1 parts NULL
    2 processor 1
    3 amd 2
    4 intel 2
    5 hard disk 1
    6 memory 1
    7 ddr1 6
    8 ddr2 6
    9 ddr3 6
    10 533Mhz 7
    11 667Mhz 8
    12 800Mhz 8
    etc

    Products
    ---------
    id--- name---- categoryID--- description--- etc..
    1 amd phenom 3
    2 amd sempron 3
    3 amd athlon 3
    4 intel core 2 duo 4
    4 intel core i3-- 4
    5 1GB DDR2 667Mhz --11
    etc

    What i need to do is for each sub category like say under processors we have AMD and Intel, then I would want to get 5 random products from AMD and Intel. I would want this for each category and sub category. Another example is say Memory, I would like to get 5 random products from DDR1, DDR2 and DDR3.
    Many Thanks for your help and advice.
    Regards.

  7. #7
    Join Date
    Jan 2012
    Posts
    84
    Try this query.

    Code:
    go
    with tree( id, name, parentid, leafid)
    as (
    	select id, name, parentid, id
    	from category c
    	where not exists (
    		select 1 from category c1
    		where c.id = c1.parentid
    	)
    	union all
    	select c.id, c.name, c.parentid, t.leafid
    	from category c
    	join tree t
    	on t.parentid = c.id
    ), brumba as (
    	select t.id categoryid, t.name categoryname,
            p.id productid, p.name productname,
            row_number() over( partition by t.id order by newid()) rn      
    	from tree t
    	join products p on t.leafid = p.categoryId
    )
    select * from brumba
    where rn <= 5
    order by categoryid, productid;

  8. #8
    Join Date
    Jul 2009
    Posts
    168
    Thanks Kordirko for help but query produces an error in MS Sql server 2008 as "incorrect syntax near tree". "Incorrect syntax near ',' ". Any idea?

  9. #9
    Join Date
    Jan 2012
    Posts
    84
    I don't know.
    I am from oracle world, I've just started to learn MS SQL,
    maybe some MS expert would know the reason of this error ?

    This is my test case and it works fine:
    Code:
    SELECT @@VERSION;
    
    ----------------------------------------------------------------------------------------------------------------------------------
    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) 
    	Apr  2 2010 15:53:02 
    	Copyright (c) Microsoft Corporation
    	Express Edition with Advanced Services on Windows NT 5.1 <X86> (Build 2600: Dodatek Service Pack 3)
    
    
    (1 row(s) affected)
    
    
    go
    SELECT substring( table_name, 1, 10 ) tabname,
    	   ordinal_position,
    	   substring( column_name, 1, 10 ) colname,
    	   substring( data_type, 1, 10),
    	   is_nullable,character_maximum_length 
    FROM information_schema.COLUMNS
    WHERE table_name LIKE '%category%' or TABLE_NAME like '%products%'
    ORDER BY tabname, ordinal_position
    ;
    
    tabname    ordinal_position colname               is_nullable character_maximum_length
    ---------- ---------------- ---------- ---------- ----------- ------------------------
    category   1                id         int        NO          NULL
    category   2                name       varchar    YES         100
    category   3                parentid   int        YES         NULL
    products   1                id         int        NO          NULL
    products   2                name       varchar    YES         100
    products   3                categoryId int        YES         NULL
    
    (6 row(s) affected)
    
    
    go
    with tree( id, name, parentid, leafid)
    as (
    	select id, name, parentid, id
    	from category c
    	where not exists (
    		select 1 from category c1
    		where c.id = c1.parentid
    	)
    	union all
    	select c.id, c.name, c.parentid, t.leafid
    	from category c
    	join tree t
    	on t.parentid = c.id
    ), brumba as (
    	select t.id categoryid, t.name categoryname,
            p.id productid, p.name productname,
            row_number() over( partition by t.id order by newid()) rn      
    	from tree t
    	join products p on t.leafid = p.categoryId
    )
    select categoryid,
           SUBSTRING( categoryname, 1, 10),
           productid,
           SUBSTRING( productname, 1, 10),
           rn
    from brumba
    where rn <= 5
    order by categoryid, productid;
    
    
    categoryid             productid              rn
    ----------- ---------- ----------- ---------- --------------------
    1           parts      1           amd phenom 4
    1           parts      2           amd sempro 5
    1           parts      4           intel core 3
    1           parts      5           intel core 2
    1           parts      6           1GB DDR2 6 1
    2           processor  1           amd phenom 1
    2           processor  2           amd sempro 3
    2           processor  3           amd athlon 4
    2           processor  4           intel core 5
    2           processor  5           intel core 2
    3           amd        1           amd phenom 2
    3           amd        2           amd sempro 3
    3           amd        3           amd athlon 1
    4           intel      4           intel core 2
    4           intel      5           intel core 1
    6           memory     6           1GB DDR2 6 1
    8           ddr2       6           1GB DDR2 6 1
    11          667Mhz     6           1GB DDR2 6 1
    
    (18 row(s) affected)
    Last edited by kordirko; 01-28-12 at 07:18.

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    CREATE table #Category(
    	id		INT	NOT NULL,
    	name	VARCHAR(20)	NOT NULL,
    	parentid	INT	NOT NULL
    )
    
    INSERT INTO #Category (id, name, parentid) VALUES
    (1, 'parts', 1),
    (2, 'processor', 1),
    (3, 'AMD', 2),
    (4, 'Intel', 2),
    (5, 'Hard Disk', 1),
    (6, 'Memory', 1),
    (7, 'DDR1', 6),
    (8, 'DDR2', 6),
    (9, 'DDR3', 6),
    (10, '533Mhz', 7),
    (11, '667Mhz', 8),
    (12, '800Mhz', 8)
    
    CREATE table #Products(
    	id		INT	NOT NULL,
    	name	VARCHAR(20)	NOT NULL,
    	categoryId	INT	NOT NULL
    )
    
    INSERT INTO #Products (id, name, categoryId) VALUES
    (1, 'AMD Phenom', 3),
    (2, 'AMD Sempron', 3),
    (3, 'AMD Athlon', 3),
    (4, 'Intel core 2 duo', 4),
    (5, 'Intel core i3', 4),
    (6, 'DDR2 1GB 6667Mhz', 11),
    (7, 'Intel 8080', 4),
    (8, 'Intel 80286', 4),
    (9, 'Intel 80386', 4),
    (10, 'Intel 80486', 4),
    (11, 'Intel Pentuim', 4),
    (12, 'Intel Pentium I', 4)
    
    
    DECLARE @TopLevelId	INT;
    SET @TopLevelId = 2;
    
    WITH CTE AS
    (SELECT id, name, parentid, id as Adam, 1 as level
    FROM #Category
    WHERE id = @TopLevelId
    	UNION ALL
    SELECT #Category.id, #Category.name, #Category.parentid, CTE.Adam, CTE.level + 1
    FROM #Category 
    	INNER JOIN CTE ON
    		#Category.parentid = CTE.id
    WHERE #Category.id <> @TopLevelId
    ),
    Prods AS
    (SELECT id, 
    	name, 
    	categoryId,
    	NewId() as GUID_
    FROM #Products
    ),
    Prods5 AS
    (SELECT  id, 
    	name, 
    	categoryId,
    	ROW_NUMBER() OVER (PARTITION by categoryId ORDER BY GUID_) as RowNum
    FROM Prods
    )
    SELECT CTE.id, CTE.name, Prods5.id, Prods5.name
    FROM CTE
    	LEFT OUTER JOIN Prods5 ON
    		CTE.id = Prods5.categoryId AND
    		Prods5.RowNum <= 5
    ORDER BY level, CTE.id
    
    
    DROP table #Category
    DROP table #Products
    It will give you another result each time you run it.

    What version of SQL Server are you using? I tested this on 2008.
    Last edited by Wim; 01-28-12 at 08:41. Reason: Sorry, for double posting. Only noticed Kordirko's response after submit.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Jul 2009
    Posts
    168
    Thanks kordirko for your answer, looks cool and the results exactly as i need. Hopefully, some people using Oracle might find the solution helpful but this is the result I expect as our friend WIM tried to give us but am getting this error:

    "The OVER SQL construct or statement is not supported."

    SQL Server is 2008 R2 Express and when I click the About from the Configuration Manager I get this:

    SQL Server Configuration Manager
    Microsoft Corporation
    Version: 2007.0100.1600.022

    I am using Visual Web Developer Express 2010 so the Sql Server Express 2008 R2 is the compact and in-built version and cannot be used externally. Thanks.

  12. #12
    Join Date
    Jul 2009
    Posts
    168
    any luck at all Wim??

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Thanks kordirko for your answer, looks cool and the results exactly as i need.
    Wasn't your problem solved then?

    For a minute I thought "row_number() over" isn't supported in the Express edition, but it is. The code as shown runs perfectly on my machine.

    Can you run this code?
    Code:
    USE AdventureWorks;
    GO
    SELECT c.FirstName, c.LastName
    	,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS 'Row Number'
    	,RANK() OVER (ORDER BY a.PostalCode) AS 'Rank'
    	,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS 'Dense Rank'
    	,NTILE(4) OVER (ORDER BY a.PostalCode) AS 'Quartile'
    	,s.SalesYTD, a.PostalCode
    FROM Sales.SalesPerson s
    	INNER JOIN Person.Contact c
    		ON s.SalesPersonID = c.ContactID
    	INNER JOIN Person.Address a
    		ON a.AddressID = c.ContactID
    WHERE TerritoryID IS NOT NULL
    	AND SalesYTD <> 0;
    Last edited by Wim; 02-01-12 at 19:05.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  14. #14
    Join Date
    Jul 2009
    Posts
    168
    Thanks wim. No, I meant that was what i required as results but the solution was for Oracle World I cant get the above query to work as I haven't got the database required. Any advice or can you give me a simple query for my example table so I can run to see if "row_number() over" works? Many thanks.

  15. #15
    Join Date
    Jul 2009
    Posts
    168
    Hi again wim, managed to get the query with AdventureWorks to work although it gave me the error 'declare sql' not supported which i ignored and continued and got me some results. However, when I run your previous code for my actual data still ignoring the 'not supported' message, I get 'invalid columns id and name'??
    Think I made a slight mistake in naming my columns up there while my actual tables are :

    Category
    --------
    id
    name
    parentid

    Products
    --------
    productID
    description
    Last edited by kpeeroo; 02-02-12 at 07:44.

Posting Permissions

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