SQL script for menu structure

[Srcee]

I want to create menu structure from a database.

For example, the database is the following:

id name parent
1 x null
2 y 1
3 z null
4 a 3
5 b 2

The result should be:

-x
--y
---b
-z
--a

Now, the algorithm I have on mind is the following:
1. first find a column which has 'parent' = null. (I'll call this column A)
2. then find a column which has 'parent' = A's parent. (I'll call this column B)
3. then find a column which has 'parent' = B's parent.
etc.

WHILE (SELECT MenuText FROM dbo.Table2 WHERE parent = NULL)
BEGIN
   SELECT MenuText FROM dbo.Table2 WHERE parent = ...
END

Is it good?

[buddylee17]

I'm assuming by menu, that you mean a drop-down menu populated from a web application. Being a programmer, you'll have the tendency to perform iterations over result sets. This will perform very poorly when your row count gets higher. Save the iterations for after the db gives you back the results. Moreover, think in SETS.

Your algorithm is correct, however this will not be optimal with a relational database. A relational database can retrieve 1 row as fast as it can 1000. However, retrieving 1000 rows, 1 row at a time will be very slow.

Get the entire result set from the db, then let the app format the presentation layer (query assumes the table name is Menu):

SELECT ROW_NUMBER() OVER (ORDER BY Parent.name) MenuId, Parent.name Level1, ChildFirst.name Level2, ChildSecond.name Level3
FROM Menu Parent
	LEFT OUTER JOIN Menu ChildFirst ON Parent.id = ChildFirst.parent
		LEFT OUTER JOIN Menu ChildSecond ON ChildFirst.id = ChildSecond.parent
WHERE Parent.parent IS NULL

The Left outer join is only required because there is no certainty that every parent has a child. Additionally, the ROW_NUMBER function is only available in sql 2005 and up.