多表查询技术咨询：如何关联无关联表获取指定列与计算列

Got it, let's work through this step by step. Since Table3 has no explicit relationship with Table1 or Table2, the way to combine its data with the other tables is using a CROSS JOIN—this will pair every row from Table3 with every row from your Table2/Table1 dataset. If you only need specific columns from Table2 (and don't need Table1 data beyond linking to Table2), here's how to structure your query:

SELECT
    -- Column from Table3 and your calculated new column
    t3.[ColumnFromTable3],
    CASE 
        WHEN t3.[ColumnFromTable3] IN ('0001', '7004', '1004', '7001', '8001', '7014', '7012', '7015', '7006') THEN 'R1'
        WHEN t3.[ColumnFromTable3] IN ('9001', '9017') THEN 'R2'
        WHEN t3.[ColumnFromTable3] IN ('9003', '9006') THEN 'R3'
        WHEN t3.[ColumnFromTable3] IN ('9005', '9008', '9004') THEN 'R4'
        ELSE 'OTHER' 
    END AS [NewColumn3],
    -- Desired column from Table2
    t2.[ColumnFromTable2]
FROM
    [dbo].[Table3] t3
-- Combine every Table3 row with every Table2 row (since no shared keys)
CROSS JOIN [dbo].[Table2] t2
-- Optional: Link Table2 to Table1 if you need to filter/get Table1 data
INNER JOIN [dbo].[Table1] t1 ON t2.ID_Table1 = t1.ID
-- Optional: Add filters to reduce the result set (example below)
-- WHERE t1.SomeFilterColumn = 'TargetValue' OR t3.ColumnFromTable3 != 'OTHER'

A quick note on CROSS JOIN: #

This creates a Cartesian product—meaning the total number of rows returned will be (number of rows in Table3) * (number of rows in Table2). If your tables are large, this could result in a massive dataset. If you actually intended to compare values between Table3 and Table2 (instead of pairing all rows), you might need to adjust the logic (e.g., using a LEFT JOIN with a value-based condition). But based on your description of no existing relationships, CROSS JOIN is the standard approach here.

内容的提问来源于stack exchange，提问作者disaster