Sample Header Ad - 728x90

Doesn't T-SQL support correlated CROSS JOINs?

0 votes
1 answer
313 views
This query works SELECT QMC.HAUSKEY, t1.STRNAME, t2.HAUS_NR FROM SWOPS.MIGR.QMCAddresses QMC CROSS JOIN (SELECT STRNAME FROM SWOPS.MIGR.EB_DICT_STREET_QMC WHERE FK=8055909) t1 CROSS JOIN (SELECT HAUS_NR FROM SWOPS.MIGR.EB_DICT_HAUSNR_QMC WHERE FK=8055909) t2 WHERE 1=1 AND QMC.HAUSKEY=8055909 ORDER BY HAUSKEY, t1.STRNAME, t2.HAUS_NR enter image description here However, when I replace the constant 8055909 in the CROSS JOIN subqueries with the column reference QMC.HAUSKEY the query reports an error `Msg 4104, Level 16, State 1, Line 56 The multi-part identifier "QMC.HAUSKEY" could not be bound.` Why is that? BACKGROUND: QMCAddresses contains the buildings and their addresses EB_DICT_STREET_QMC contains the possible variations of the street-names of the addresses in QMCAddresses EB_DICT_STREET_HAUSNR contains the possible variations of the house-numbers of the addresses in QMCAddresses What I am trying to do is to create a cartesian product for each building address consisting of all possible variations of its street-name and all possible variations of its house-number. e.g. enter image description here
Asked by Mediterrano (1 rep)
Sep 7, 2022, 02:11 PM
Last activity: Sep 7, 2022, 09:49 PM