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

Question

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.

Answer 1

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

Answer 2

Then number of columns must match between both parts of the union.

In order to build the full path, you need to "aggregate" all values of the Location column. You still need to select the id and other columns inside the CTE in order to be able to join properly. You get "rid" of them by simply not selecting them in the outer select:

with q as  (    select ID, PartOf_LOC_id, Location, ' > ' + Location as path    from tblLocation     where ID = 1      union all     select child.ID, child.PartOf_LOC_id, Location, parent.path + ' > ' + child.Location     from tblLocation child      join q parent on parent.ID = t.LOC_PartOf_ID ) select path from q;