0

What I need. Same values for category tree as in first case, first case is for name (cte), second is for id (cte2).

What I tried.

with recursive

cte as(
select category_id,category_parent,category_name::text
from s_category
where category_parent = 0
union all
select
s_category.category_id,
s_category.category_parent,
concat(cte.category_name, ' > ', s_category.category_name)
from s_category,cte
where cte.category_id = s_category.category_parent
),

cte2 as(
select category_id,category_parent,category_name::text
from s_category
where category_parent = 0
union all
select
s_category.category_id,
s_category.category_parent,
concat(cte2.category_id, ' : ', s_category.category_id)
from s_category,cte2
where cte2.category_id = s_category.category_parent
)

select
cte.category_name as "category",
cte2.category_id as "hierarchy",

...

cte return this (correct),

Bydlení > Barvy a laky > Vnitřní barvy

cte2 return this (not correct, did it by same way, but it return only last id),

6669

instead

6111 : 6333 : 6669

Where is the catch?

Thanks.

Update
Here is Akina's working solution. Thank you.

with recursive
cte as(
select
category_id,
category_parent,
category_name::text,
category_id::text category_ids
from s_category as c
where category_parent = 0
union all
select
c.category_id,
c.category_parent,
concat(cte.category_name, ' > ', c.category_name),
concat(cte.category_ids, ':', c.category_id::text)
from s_category as c,cte
where cte.category_id = c.category_parent
)

select distinct
concat('Úvod > ',cte.category_name) as "category",
s_category.category_id as "categoryID",
cte.category_ids as "hierarchy",

...

from

...

left join cte on s_product.category_id = cte.category_id

...

group by

...

cte.category_name,
cte.category_ids
  • Where is the catch? category_id must be explicitly converted from number to string. And you do not need 2 CTEs, obtain both concatenations in the same one. – Akina Jul 24 '19 at 7:32
  • I agree with @Akina on both cases, in addition cte2 as(...category_name::text looks suspicious. Provide a minimal executable example demonstrating your problem, and I'm sure someone will assist. – Lennart Jul 24 '19 at 7:44
  • In addition, you should declare the columns in your cte: cte2 (c1, ...) as (... – Lennart Jul 24 '19 at 8:05
  • Hi again, here is the example. dbfiddle.uk/… . I wanna do it same thing for id, example 1234:2345:3456, etc... – Stanislav Hosek Jul 24 '19 at 9:35
  • @Akina, I tried everything about converting int to string, but still false - 'UNION types integer and text cannot be matched'. I don't understand why. Could you please help me with this? Thank you in advance. Here is the code without 'cast' and '::text' modifications. dbfiddle.uk/… – Stanislav Hosek Jul 24 '19 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.