> Hello,
>
> at the moment, I’m in struggle with a CTE:
>
> for with recursive ok as
> (select a.pos, b.kennzeichen, b.price, b.id from t_l_prkom_grp a inner
> join t_l_prkom_grp_pos b on a.id = b.id_kom_grp where a.id_kom =
> :id_kom union all select c.pos, d.kennzeichen, d.price, d.id from
> t_l_prkom_grp c inner join t_l_prkom_grp_pos d on c.id = d.id_kom_grp
> inner join ok on c.pos = ok.pos+1 where c.id_kom = :id_kom order by
> c.pos)
>
> I would like all combinations, it works fine, but the last records are
> twice.
>
> (pos 1,2,3 – each pos has in t_l_prkon_grp 3 records, combinations
> like this. (kennzeichen) 00, 01, 02 for pos 1, 10, 11, 12 for pos 2,
> 20, 21, 22 für pos 3 = 00-10-20, 00-10-21, 00-10-22, 00-11-20…)
>
> As of 02-12-22 (the last combination) it begins with 02-10-20 to
> 02-12-22, then twice the last 02-12-20 to 02-12-22 too.
>
> What can be wrong?
>
> Best regards.
>
> Olaf

Hi again, Olaf!

First iteration creates 9 rows, three for each pos.
Second iteration takes the 3 with pos1 and pos2 and joins with the three with pos2 and pos3, giving 9 more rows with pos2 and 9 with pos 3. Third iteration takes those 9 with pos2 and joins with the three with pos3 creating 27 more rows. Hence, the CTE generates 3+3+3+9+9+27 rows, i.e.
54 rows. These 54 rows include plenty of duplicates, e.g. you have 13 identical rows with 3-90-4-37. How to solve it? I don't know, so far you've only described your problem in a way that leaves lots of guesswork for anyone that tries to help you.

My guess would be that you don't want WITH RECURSIVE at all, but are simply looking for something like

select t1.kennzeichen || coalesce('-' || t2.kennzeichen,'') || coalesce('-' || t3.kennzeichen,'') from t_l_prkom_grp_pos t1 left join t_l_prkom_grp_pos t2 on t1.id_kom = t2.id_kom and t1.pos + 1 = t2.pos left join t_l_prkom_grp_pos t3 on t1.id_kom = t3.id_kom and t1.pos + 2 = t3.pos where t1.pos = 1
and t1.id_kom = 2

If you're actually looking for something else, then please describe exactly what result you're looking for. You did show two table definitions and insert statements to fill these - it was great that you supplied this, although you ought to also have checked that all fields were defined (your definition of T_L_PRKOM_GRP_POS only contained five fields, whereas your insert statements assumed nine fields to be present). Please also show us the correct and complete output you want from your query/stored procedure given the data you've already supplied, you may supply some further textual information, but what is most important is the actual desired result set.

HTH,
Set

Hello set,

this construct can have more than three layers, that is my problem and therefore I would like to use a recursive cte.

I have found a solution, I have limited the first iteration with "and pos = 1". I had also create a new field with the Id oft the previous category - in this case the previous category id oft he first position is empty and I have take this for a criteria.

I think, this can work for all conditions.

Thank you for your help 😊

with best regards

Olaf