Error - "UNION operator must have an equal number of expressions" when using CTE for recursive selection

recursive-query, sql, sql-server, t-sql

Solution

The problem lays here:

--This result set has 3 columns
select LOC_id,LOC_locatie,LOC_deelVan_LOC_id from tblLocatie t
where t.LOC_id = 1 -- 1 represents an example

union all

--This result set has 1 columns   
select t.LOC_locatie + '>' from tblLocatie t
inner join q parent on parent.LOC_id = t.LOC_deelVan_LOC_id

In order to use `union` or `union all` number of columns and their types should be identical cross all result sets.

I guess you should just add the column `LOC_deelVan_LOC_id` to your second result set

Problem

At this moment I have a table `tblLocation` with columns `ID, Location, PartOfID`. The table is recursively connected to itself: `PartOfID -> ID` My goal is to have a select output as followed: ``` > France > Paris > AnyCity > ``` Explanation: AnyCity is located in Paris, Paris is located in France. My solution that I found until now was this: ``` ; with q as ( select ID,Location,PartOf_LOC_id from tblLocatie t where t.ID = 1 -- 1 represents an example union all select t.Location + '>' from tblLocation t inner join q parent on parent.ID = t.LOC_PartOf_ID ) select * from q ``` Unfortunately I get the following error: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. If you have any idea how I could fix my output it would be great.

Original source