Re: implementing hierarchy

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 02/15/05 

Date: Tue, 15 Feb 2005 13:22:39 -0500

It is not all that hard to do without all of these columns. I have a post
in here:
http://tinyurl.com/64mbz

That shows one fairly simple way using a loop per level (which is exactly
what the connect by prior, and CTE's in 2005 will do, though cleaner.)
Then Joe C has a post where he gives a more complex version that has some
benefits, but is still quite messy, depending on your actual needs. 

-- 
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design - 
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in 
consulting services.  All other replies may be ignored :)
"Flip" <[remove_me]phenry_w@hotmail.com> wrote in message 
news:usJXed4EFHA.2180@TK2MSFTNGP12.phx.gbl...
> I'm an MS person, I love SQL Server, but the one feature I would wish MS 
> would implement is an easy way to get hierarchical information out of SQL 
> Server!
>
> Maybe I'm missing something, but I've read a few articles about trees like 
> this one (http://www.developerfusion.co.uk/show/4633/) and all of them 
> talk about implementing additional columns, triggers and stored procs! 
> Why is implementing a self lookup so complicated?  Oracle has a simple 
> (comparitively speaking) connect by prior command.  If you know of an 
> easier way (without additional columns) to do this, I would love to hear 
> from you!
>
> All I want to do is setup a table that has an employee id and a manager 
> id, simple, easy to understand, insert, etc.  I don't see any reason to 
> have additional columns for lineage, path, or other what seem to be 
> cludges to a missing simple command.  Please show me an easy way to do 
> this?
>
> Thank you.
>

Relevant Pages

  • implementing hierarchy
    ... I'm an MS person, I love SQL Server, but the one feature I would wish MS ... would implement is an easy way to get hierarchical information out of SQL ... Maybe I'm missing something, but I've read a few articles about trees like ... connect by prior command. ...
    (microsoft.public.sqlserver.programming)
  • Re: implementing hierarchy
    ... :> I haven't read the link Louis, ... > I'm an MS person, I love SQL Server, but the one feature I would wish MS ... > connect by prior command. ... > cludges to a missing simple command. ...
    (microsoft.public.sqlserver.programming)
  • Re: implementing hierarchy
    ... Tibor Karaszi, SQL Server MVP ... > a simple connect by prior command. ... > other what seem to be cludges to a missing simple command. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Server Parameters
    ... I understand what SQL Server is doing and it is doing as ... > have a missing param or misspelled it, ... >> a SQL error back that Procedure XYZ is expecting Parameter '@myParameter' ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How to enable Windows Authentication
    ... either your missing something or I am missing something and I ... Windows Local User accounts. ... I remove the comments from the connection string in the web.config I can ... When connecting to SQL Server 2005, this failure may be caused by the ...
    (microsoft.public.dotnet.framework.aspnet)