I have a SQL Server 2008 table with three columns, ID, ProvinceID and Name
ID and ProvinceID are both integers. ID is an identity for each item and ProvinceID is either 0 or an ID entry to indicate its parent. A sample of the table count as entered is below:
ID ProvinceID Name
1 9 Munster
2 1 Cork
3 9 Leinster
4 3 Dublin
5 1 Waterford
6 3 Wexford
7 3 Louth
8 1 Tipperary
9 0 Ireland
I wish to open the table so that it lists the records in parent - child - grandchild, for example:
9 0 Ireland
3 9 Leinster
4 3 Dublin
7 3 Louth
6 3 Wexford
1 9 Munster
2 1 Cork
8 1 Tipperary
5 1 Waterford
I have been told that a Common Table Expression should be able to do what I am looking for but have been unable to get it to work. The last entry in the table (Ireland) was added as the sample on the MSDN site was uaing a NULL for the equivalent field.
I have the following script in SQL Server Management Studio to try achieve this. It does not indicate any issues at all.
DECLARE @oldCountryPath as nvarchar(10) = 'x';
WITH search_graph (ID,ProvinceID,Name, ProvDepth, CountryPath )
AS (
SELECT [ID],[ProvinceID],[Name], 0 AS ProvDepth, 'x' as CountryPath
FROM [Counties]
WHERE [ID] = 1
UNION ALL
SELECT e.ID, e.ProvinceID, e.Name, d.ProvDepth + 1 AS ProvDepth,
d.CountryPath + CONVERT(nvarchar, 'x' )as CountryPath
FROM [Counties] AS e
INNER JOIN search_graph AS d
ON e.ProvinceID = d.ID
WHERE e.ProvinceID = d.ID
)
SELECT [ID],[ProvinceID],[Name], ProvDepth, CountryPath
FROM search_graph
ORDER BY [ID],[ProvDepth]
However, when I execute it I get this error:
Types don't match between the anchor and the recursive part in column "CountryPath" of recursive query "search_graph".
Can anyone tell me what I am missing or am doing wrong?
Thanks in advance.