Subqueries (parent/child) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Subqueries (parent/child)

Author

Topic

micke802
Starting Member

3 Posts

Posted - 2007-06-03 : 04:19:36

Have a query that looks like this.


SELECT GROUP_ID, NAME, PARENT_ID
FROM TABLE
WHERE PARENT_ID IN
(SELECT PARENT_ID 
FROM TABLE AS T1 
WHERE GROUP_ID = 17 'were 17 is a querystring value 
OR PARENT_ID IN
(SELECT PARENT_ID 
FROM TABLE AS T2 
WHERE T2.GROUP_ID = T1.PARENT_ID))
OR PARENT_ID = 0

DB looks like this:
GROUP_ID, NAME, PARENT_ID
10, "AUDI", 0
11, "BMW", 0
12, "A4", 10
13, "A6", 10
14, "3-SERIES", 11
15, "5-SERIES", 11
16, "A4 Quattro", 12
17, "A6 Quattro", 13

Result i get from query
10, "AUDI", 0
11, "BMW", 0
16, "A4 Quattro", 12
17, "A6 Quattro", 13

Result i want:
10, "AUDI", 0
11, "BMW", 0
12, "A4", 10
13, "A6", 10
16, "A4 Quattro", 12
17, "A6 Quattro", 13

What i'm I doing wrong?!

BR
Micke

pootle_flump

1064 Posts

Posted - 2007-06-03 : 09:34:02

Hi

What's the business logic? What, in plain English, are you trying to do? It is much easier to work from there than infer from broken code.

micke802
Starting Member

3 Posts

Posted - 2007-06-03 : 14:35:46

It the menu for the site. With the querystring value (17 in this case) i want to get the child and parent objects in the menu.

pootle_flump

1064 Posts

Posted - 2007-06-03 : 16:27:20

Ah. It looks like more than just that - you want all the cousins, sisters, brothers etc.. Basically find the ultimate parent and then all of the children of that parent yes?

Is there a fixed limit on the number of children any parent might have?

pootle_flump

1064 Posts

Posted - 2007-06-03 : 16:29:47

Hang on:

Result i want:
10, "AUDI", 0
11, "BMW", 0
12, "A4", 10
13, "A6", 10
16, "A4 Quattro", 12
17, "A6 Quattro", 13

Line in red an error right? If not - how does it qualify?

micke802
Starting Member

3 Posts

Posted - 2007-06-04 : 00:11:34

Sorry, a misstake, forget 16, shouln't be in the list.
10 and 11 is top level (0), 12 and 13 is children to 10 and so on.

AUDI(10)
-A4(12)
-A6(13)
--A6 Quattro(17)
BMW(11)

pootle_flump

1064 Posts

Posted - 2007-06-04 : 04:31:17

Ok - just to finalise the requirement.

For a given item you require all the item's parents and children. In addition any children of any of the item's parents (hence BMW(11) and A6(13)) but to a single depth only? Do you require the children of the children too? (the item you used in the example has no children so I cannot know from the example)

pootle_flump

1064 Posts

Posted - 2007-06-04 : 04:48:25

quote:
Originally posted by pootle_flump

Is there a fixed limit on the number of children any parent might have?

Also you forget to answer this question. This affects whether or not recursion is required.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-06-06 : 05:22:33

First query gives the result:
AUDI(10)
-A4(12)
-A6(13)
--A6 Quattro(17)
BMW(11)

The second query shows how you can build a breadcrumb trail for your menu, might be useful for you.

SET NOCOUNT ON
CREATE TABLE #T(GROUP_ID INT,NAME VARCHAR(20),PARENT_ID INT)
INSERT #T SELECT 10, 'AUDI', 0
INSERT #T SELECT 11, 'BMW', 0
INSERT #T SELECT 12, 'A4', 10
INSERT #T SELECT 13, 'A6', 10
INSERT #T SELECT 14, '3-SERIES', 11
INSERT #T SELECT 15, '5-SERIES', 11
INSERT #T SELECT 16, 'A4 Quattro', 12
INSERT #T SELECT 17, 'A6 Quattro', 13

-- gives your result
;WITH X2 AS
(
	SELECT	PARENT_ID
	FROM	#T
	WHERE	GROUP_ID = 17
	UNION ALL
	SELECT	#T.PARENT_ID
	FROM	#T JOIN X2 ON #T.GROUP_ID = X2.PARENT_ID
)
SELECT	*
FROM	#T
WHERE	PARENT_ID IN(SELECT PARENT_ID FROM X2)
UNION
SELECT	*
FROM	#T
WHERE	PARENT_ID = 0
ORDER BY
		PARENT_ID
		,GROUP_ID

-- menu with breadcrumb trail
;WITH X(GROUP_ID,TRAIL) AS
(
	SELECT	GROUP_ID, CAST(LTRIM(GROUP_ID) AS VARCHAR(MAX)) AS TRAIL
	FROM	#T
	WHERE	PARENT_ID = 0
	UNION ALL
	SELECT	#T.GROUP_ID, X.TRAIL + '.' + CAST(LTRIM(#T.GROUP_ID) AS VARCHAR(MAX))
	FROM	#T JOIN X ON #T.PARENT_ID = X.GROUP_ID
)
SELECT	X.TRAIL
		,#T.GROUP_ID
		,#T.NAME
		,#T.PARENT_ID
		,REPLICATE(' - ',LEN(TRAIL)-LEN(REPLACE(TRAIL,'.',''))) + #T.NAME
FROM	X JOIN #T ON X.GROUP_ID = #T.GROUP_ID
ORDER BY X.TRAIL

DROP TABLE #T

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources