Converting Multiple 'old format' Joins in WHERE Clause

Question

I have the following structure of code in SQL Server. It uses multiple old format *= joins in the WHERE clause?

SELECT ....
FROM (select ...) L1,
     (select ...) L2,
     (select ...) SE,
     (select ...) EX,
     (select ...) SE,
     (select ...) Y1,
     (select ...) Y2,
     (select ...) Y3,
     (select ...) Y4,
     (select ...) Y5
WHERE SE.FV = EX.FV
    and SE.FV *= Y1.FV
    and SE.FV *= Y2.FV
    and SE.FV *= Y3.FV
    and SE.FV *= Y4.FV
    and SE.FV *= Y5.FV
    and SE.L1 *= L1.FV
    and SE.L2 *= L2.FV
GROUP BY L1.FV, L1.Descr, L2.FV, L2.Descr
ORDER BY L1.FV, L1.Descr, L2.FV, L2.Descr

In the real code, the selects are a mother of a mish-mash of dynamically generated sql. But in essence, the STRUCTURE is as I've shown above.

What should the STRUCTURE of the above template be, so that it uses proper LEFT OUTER JOIN syntax, without changing the logic of the query?

Would something like this work perhaps?

SELECT L1.FV, L1.Descr, L2.FV, L2.Descr, Y1.B, Y1.A, Y2.B, Y2.A, ...
FROM (select ...) SE
     LEFT OUTER JOIN (select ...) Y1 ON SE.FV = Y1.FV
        AND LEFT OUTER JOIN (select ...) Y2 ON SE.FV = Y2.FV
        AND LEFT OUTER JOIN (select ...) Y3 ON SE.FV = Y3.FV
        AND LEFT OUTER JOIN (select ...) Y4 ON SE.FV = Y4.FV
        AND LEFT OUTER JOIN (select ...) Y5 ON SE.FV = Y5.FV
        AND LEFT OUTER JOIN (select ...) L1 ON SE.L1 = L1.FV
        AND LEFT OUTER JOIN (select ...) L2 ON SE.L2 = L2.FV,
     (select ...) EX
WHERE SE.FV = EX.FV
GROUP BY L1.FV, L1.Descr, L2.FV, L2.Descr
ORDER BY L1.FV, L1.Descr, L2.FV, L2.Descr

Answer 1

You may want to separate the "mishmash of dynamically generated sql" from the STRUCTURE by using CTEs like this:

WITH 
    SE AS (select 1 AS X, 2 AS FV, 3 AS L1, 4 AS L2),
    EX AS (select 1 AS X, 2 AS FV),
    Y1 AS (select 1 AS X, 2 AS FV),
    Y2 AS (select 1 AS X, 2 AS FV),
    Y3 AS (select 1 AS X, 2 AS FV),
    Y4 AS (select 1 AS X, 2 AS FV),
    Y5 AS (select 1 AS X, 2 AS FV),
    L1 AS (select 1 AS X, 3 AS FV, 'y' AS Descr),
    L2 AS (select 1 AS X, 4 AS FV, 'z' AS Descr)
SELECT L1.FV, L1.Descr, L2.FV, L2.Descr
FROM 
     SE
     INNER JOIN EX ON EX.FV = SE.FV
     LEFT JOIN Y1 ON Y1.FV = SE.FV
     LEFT JOIN Y2 ON Y2.FV = SE.FV
     LEFT JOIN Y3 ON Y3.FV = SE.FV
     LEFT JOIN Y4 ON Y4.FV = SE.FV
     LEFT JOIN Y5 ON Y5.FV = SE.FV
     LEFT JOIN L1 ON L1.FV = SE.L1
     LEFT JOIN L2 ON L2.FV = SE.L2
GROUP BY L1.FV, L1.Descr, L2.FV, L2.Descr
ORDER BY L1.FV, L1.Descr, L2.FV, L2.Descr