showing just 1 parent. - 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 2000 Forums

SQL Server Development (2000)

showing just 1 parent.

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2010-06-22 : 04:52:45

Hi There,

Can someone help me please.
I have 2 tables. Element & Sub-Element.
I want to display all sub-elements based on the selection of an element but I keep getting the parent shown twice.
Element: Autoid, Description
Sub-Element: AutoId, ElementId, Description

When I query I keep Getting.
+- Element -+ Sub-Element -+
+- 1.1 Oil -+ Slipage -+
+- 1.1 Oil -+ Spillage -+
+- 1.1 Oil -+ Reporting -+

I need it to show:
+- Element -+ Sub-Element -+
+- 1.1 Oil -+ Slipage -+
+- -+ Spillage -+
+- -+ Reporting -+

How can I do that.

Best Regards,

Steve.

Steve

Manjunathcbhat
Starting Member

2 Posts

Posted - 2010-06-22 : 05:25:30

Hi Steve,

Is it the Records u are talking about.

Thanks and Regards,
Manjunath C Bhat

Manjunathcbhat
Starting Member

2 Posts

Posted - 2010-06-22 : 05:27:12

Hi Steve,

Try this out. Guess this might help you out.

DECLARE @MasterTableName AS VARCHAR(255)='YourMasterTableName'
;WITH YOURCTENAME AS
(
--initialization
SELECT object_name(referenced_object_id) as ParentTable, object_name(parent_object_id) as ChildTable, 1 as hlevel
FROM sys.foreign_keys
WHERE object_name(referenced_object_id) =@MasterTableName
UNION ALL
--recursive execution
SELECT object_name(e.referenced_object_id), object_name(e.parent_object_id),m.hlevel + 1
FROM sys.foreign_keys e INNER JOIN YOURCTENAME m
ON object_name(e.referenced_object_id) = m.ChildTable --.object_name(parent_object_id)
and not (m.ChildTable = object_name(e.parent_object_id) and m.ParentTable = object_name(e.referenced_object_id)) ----Used this because Might Go in Infinite if some table is self Referenced
)
SELECT distinct childtable, hlevel FROM YOURCTENAME
order by hlevel desc
option (maxrecursion 32767);

Thanks and Regards,
Manjunath C Bhat

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-22 : 05:43:11

quote:
Originally posted by Steve2106

Hi There,

Can someone help me please.
I have 2 tables. Element & Sub-Element.
I want to display all sub-elements based on the selection of an element but I keep getting the parent shown twice.
Element: Autoid, Description
Sub-Element: AutoId, ElementId, Description

When I query I keep Getting.
+- Element -+ Sub-Element -+
+- 1.1 Oil -+ Slipage -+
+- 1.1 Oil -+ Spillage -+
+- 1.1 Oil -+ Reporting -+

I need it to show:
+- Element -+ Sub-Element -+
+- 1.1 Oil -+ Slipage -+
+- -+ Spillage -+
+- -+ Reporting -+

How can I do that.

Best Regards,

Steve.

Steve

The recordset with repeated parent information is correct.
If you need to suppress the repeated parent information then do it in your front end.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources