retrieving parent and current information

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

Transact-SQL (2000)

retrieving parent and current information

Author

Topic

krisis
Starting Member

6 Posts

Posted - 2005-04-04 : 22:05:46

I have a product_group table as:
group_id, parent_id, name
1, 0, Software
2, 0, Hardware
3, 1, Operating Systems
4, 1, Productivity
5, 1, Games
6, 2, Videocards
7, 2, Motherboards

I wish to be able to select the full path of group names. i.e. for group_id 6 to be able to grab:
Hardware, Videocards

How can I do this in a single SQL query?

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-04 : 22:22:09

Try this:

SELECT isnull(B.name + ',', '') + A.name
FROM product_group A LEFT OUTER JOIN product_group B
ON A.parent_id = B.group_id
WHERE A.group_id = 6

This will only work for a single-level hierarchy. If you have more than one level, that's a different story.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-04 : 23:22:12

http://www.sqlteam.com/item.asp?ItemID=8866

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

krisis
Starting Member

6 Posts

Posted - 2005-04-04 : 23:39:01

Thanks.

Interesting article.. however, it is only that single-level hierarchy, so the first query answer works great for now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources