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
complex.
HTH,
Set
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
