Subject Re: [firebird-support] CTE Question
Author setysvar
Hi Olaf!

First, SQL doesn't like unknown columns, you need to know at least the
maximum possible number of properties to support. Having said that, you
could try something like:

select B1.Property, B2.Property, B3.Property, B4.Property
from TableA A1
join TableB B1 on A1.ID = B1.ID_TableA
left join TableA A2 on A2.Pos = 2
left join TableB B2 on A2.ID = B2.ID_TableA
left join TableA A3 on A3.Pos = 3
left join TableB B3 on A3.ID = B3.ID_TableA
left join TableA A4 on A4.Pos = 4
left join TableB B4 on A4.ID = B4.ID_TableA
left join TableC C on C.ID_TableB_1 in (A1.ID, A2.ID, A3.ID, A4.ID)
and (C.ID_TableB_2 in (A1.ID, A2.ID, A3.ID, A4.ID)
or C.ID_TableB_2 is null)
and (C.ID_TableB_3 in (A1.ID, A2.ID, A3.ID, A4.ID)
or C.ID_TableB_3 is null)
and (C.ID_TableB_4 in (A1.ID, A2.ID, A3.ID, A4.ID)
or C.ID_TableB_4 is null)
where A1.Pos = 1
and C.ID is null

Since the left joins to TableA doesn't refer to other tables, I assume
the left joins between TableA and TableB to be the same as cross joins.

TableC is for your next step, I'm assuming TableC could be defined like:
ID, ID_TableB_1, ID_TableB_2, ID_TableB_3, ID_TableB_4

and that not red, 60W could be written like:

1, 101, 109, <null>, <null>

(assuming 101 to be red and 109 to be 60W)

It is of course thinkable that TableC also could have rows rather than
columns for properties like TableB, but that would make the query more