How to create a SELECT statement for this case

Imagine you have an online store. The depth of subcategories isn't limited, so you may get some items pretty close to the top and some all the way at the bottom. You want to display breadcrumbs, something like:

Root->Category1->SubCategory2->SubCategory3->Item256
Root->Category4->SubCategory5->SubCategory6->SubCategory7->SubCategory8 ->Item789

You have a table that stores your items, something like:
tblItems(
itemID longint,
itemName varchar(100),
parentCategory longint)

You have table for categories:
tblCategories(
catID longint,
catName varchar(100),
catParent longint)

If catParent = 0 it's a root category.

Question is - what's the most efficient way to build a SELECT query (or stored procedure, or - whatever you think is good) to get a full breadcrumb path out of database.
The only restriction - it must be solved on the side of the database, not in some code.

Any ideas? My dev teammates already puzzled, so I am bringing this here

 

Synthesize the breadcrumb html once and store it as a field in the categories table, that would be the most efficient way to query it.

 

Right, but how do you build it in the first place?

 

The concept behind what you ask is recursion.

In ms sql server a scalar-valued function would do the trick ..

USE [wikipedia]
GO
/****** Object:  UserDefinedFunction [dbo].[bread]    Script Date: 02/14/2009 17:40:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	
-- =============================================
CREATE FUNCTION [dbo].[bread] 
(
	-- Add the parameters for the function here
	@p1 smallint
)
RETURNS nvarchar(max)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result nvarchar(max)

	-- Add the T-SQL statements to compute the return value here
	SELECT @Result = 
		CASE
			WHEN parent = 0 THEN title
			ELSE dbo.bread(parent) + ' / ' + title
		END
	FROM test
	WHERE id = @p1
	-- Return the result of the function
	RETURN @Result

END

Code (markup):

hope this gets you started on the right course ..