SQL Server Never Ending when Join Two Tables

Question

I have one source table in DB. I need to do group and sum to get one bridging table, extract supplier info on the other bridging table then join the two using part_number.

If I run the subqueries separately, T1 gives me 54699 records and T2 gives approx 10 times rows of T1.

Next, I do left join, I expect it should return 54699 records, but the server engine never stops and it returns 50 million records at the time I scroll down to the end. I have to stop the query manually. I realized there must something wrong with my query, but I can not figure it out. I would appreciate it if you have any ideas. Thank you!

SELECT 
    T1.*, T2.SUPPLIER 
FROM 
    (SELECT 
         T.PART_NUMBER,T.YEAR, T.WEEK, 
         SUM(T.QTY_FILLED) TOTAL_FILLED,
         SUM(T.QTY_ORDERED) TOTAL_ORDERED, 
         COUNT(T.LINE_NUMBER) ORDER_TIMES
     FROM 
         DBO.TABLE1 T
     WHERE 
         T.YEAR IS NOT NULL 
     GROUP BY 
         PART_NUMBER, T.YEAR, T.WEEK) T1
LEFT JOIN 
    (SELECT 
         T.PART_NUMBER, T.SUPPLIER
     FROM 
         DBO.TABLE1 T) T2 ON T1.PART_NUMBER = T2.PART_NUMBER 
ORDER BY 
    T1.PART_NUMBER, T1.YEAR, T1.WEEK

I also tried the window function, but still no luck.

WITH T1 AS 
(
    SELECT 
        T.PART_NUMBER,T.YEAR, T.WEEK, 
        SUM(T.QTY_FILLED) TOTAL_FILLED,
        SUM(T.QTY_ORDERED) TOTAL_ORDERED, 
        COUNT(T.LINE_NUMBER) ORDER_TIMES
    FROM 
        DBO.TABLE1 T
    WHERE 
        T.YEAR IS NOT NULL 
    GROUP BY 
        PART_NUMBER, T.YEAR, T.WEEK
), T2 AS
(
    SELECT T.PART_NUMBER, T.SUPPLIER
    FROM DBO.TABLE1 T
)
SELECT 
    T1.*, T2.SUPPLIER 
FROM 
    T1 
LEFT JOIN 
    T2 ON T1.PART_NUMBER = T2.PART_NUMBER 
ORDER BY 
    T1.PART_NUMBER, T1.YEAR, T1.WEEK

Answer 1

First of all, it not only return 54699 rows. You do a join without distinct, so the result could be the join of 50.000 x 5.000.000 rows and it depends on the value of your table. If you use SQL 2017 or newer, try something like this:

SELECT 
         T.PART_NUMBER,T.YEAR, T.WEEK, 
         SUM(T.QTY_FILLED) TOTAL_FILLED,
         SUM(T.QTY_ORDERED) TOTAL_ORDERED, 
         COUNT(T.LINE_NUMBER) ORDER_TIMES,
         STRING_AGG (SUPPLIER, ', ') AS SUPPLIER
         
     FROM 
         DBO.TABLE1 T
     WHERE 
         T.YEAR IS NOT NULL 
     GROUP BY 
         PART_NUMBER, T.YEAR, T.WEEK