Hierarchy sql query

kpeeroo · #1 (**permalink**) 01-18-12, 04:12

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.

Wim · #2 (**permalink**) 01-18-12, 04:58

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)

kpeeroo · #3 (**permalink**) 01-18-12, 16:10

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.

kpeeroo · #4 (**permalink**) 01-18-12, 16:45

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.

Wim · #5 (**permalink**) 01-19-12, 08:15

Quote:

Originally Posted by kpeeroo

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.

kpeeroo · #6 (**permalink**) 01-27-12, 11:17

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.

kordirko · #7 (**permalink**) 01-27-12, 20:04

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;

kpeeroo · #8 (**permalink**) 01-28-12, 01:02

Thanks Kordirko for help but query produces an error in MS Sql server 2008 as "incorrect syntax near tree". "Incorrect syntax near ',' ". Any idea?

kordirko · #9 (**permalink**) 01-28-12, 05:56

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)

Wim · #10 (**permalink**) 01-28-12, 06:13

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.

kpeeroo · #11 (**permalink**) 01-28-12, 07:47

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.

kpeeroo · #12 (**permalink**) 02-01-12, 08:31

any luck at all Wim??

Wim · #13 (**permalink**) 02-01-12, 17:49

Quote:

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;

kpeeroo · #14 (**permalink**) 02-02-12, 05:07

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.

kpeeroo · #15 (**permalink**) 02-02-12, 06:34

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

Internet Services

Web Development

Digital Marketing

Consumer Tech