I have three tables in SQL Server

• TABLE_A - contains 500 rows
• TABLE_B - contains 1 million rows
• TABLE_C - contains 1 million rows

I want to select the rows from TABLE_B and TABLE_C join with TABLE_A based on a row number position from TABLE_B and TABLE_C tables.

Below is my sample query:

SELECT TOP (50), *
INTO ##tempResult
FROM TABLE_A
LEFT JOIN 
    (SELECT *
     FROM 
         (SELECT 
              memberID,
              ROW_NUMBER() OVER (PARTITION BY TABLE_A.member_id ORDER BY TABLE_A EM.UTupdateDate DESC) AS rowNum,
          FROM 
              TABLE_B
          JOIN 
              TABLE_C ON TABLE_B.memberID = TABLE_C.memberID
         )
    ) AS TABLE_subset
WHERE 
    TABLE_subset.rowNum <=2
) AS TABLE_INC ON TABLE_A.memberID = TABLE_INC.memberID
WHERE TABLE_A.colA = 'XYZ'

Here the TABLE_subset is joining entire records in TABLE_B and TABLE_C, but I want to join only the top 50 records with TABLE_A.

Is there any way to achieve this ?

CodePudding user response：

Your question and query doesn't match exactly, but CROSS APPLY is probably your friend here. The general idea is:

select TOP 50 *
from tableA a
CROSS APPLY (
   SELECT TOP 2 b.id, c.otherid
   from tableB b
   inner join tableC c
     ON c.id = b.id
   where b.id = a.id -- Here you match field between A and B
   order by b.date DESC -- order by something
) data

Now just need to adapt to your needs