The second result set
have only one column but it should have 3 columns for it to be contented to the first result set
(columns must match when you use UNION
)
Try to add ID
as first column and PartOf_LOC_id
to your result set
, so you can do the UNION
.
;
WITH q AS ( SELECT ID ,
Location ,
PartOf_LOC_id
FROM tblLocation t
WHERE t.ID = 1 -- 1 represents an example
UNION ALL
SELECT t.ID ,
parent.Location + '>' + t.Location ,
t.PartOf_LOC_id
FROM tblLocation t
INNER JOIN q parent ON parent.ID = t.LOC_PartOf_ID
)
SELECT *
FROM q