[RESOLVED] Abnormal Unrolling

[yereverluvinuncleber]

Rawhide!

[wqweto]

Here is my attempt

SELECT      SUM(t2.SomeCount)
FROM        (
            SELECT      t2.FK
                        , 1                 AS SomeCount
                        , t2.OtherStuff
                        , t2.MoreStuff
            FROM        table2 t2

            UNION ALL

            SELECT      t1.PK
                        , MAX(t1.SomeCount) - COUNT(t2.PK) AS SomeCount
                        , NULL              AS OtherStuff
                        , NULL              AS MoreStuff
            FROM        table1 t1
            LEFT JOIN   table2 t2
            ON          t2.FK = t1.PK
            GROUP BY    t1.PK
            HAVING      MAX(t1.SomeCount) - COUNT(t2.PK) <> 0
            ) t2

And here is my reasoning:
- I'm adding a new column SomeCount to t2 with a constant value of 1.
- I'm adding "missing" rows to t2 with enough SomeCount so that for each t2.FK there is enough SUM(t2.SomeCount) to match the total in t1.SomeCount. This is what the second SELECT after UNION ALL does.
- On new rows the values of OtherStuff, MoreStuff, CoolStuff, etc. are NULLs
- I'm using basic ANSI SQL-92 -- no CTEs, no windowing functions so this should work on MSSQL 2000, MS Access, dBase (probably:-))

cheers,
</wqw>