Lavy Lavy - 7 months ago 19
SQL Question

SQL Server Select Join?

I have following select results:

Select t1.ID, t1.Value1
from t1
where t1.ID=1


Result:

ID Value1
1 val1
1 val2
1 val3
1 val4


and

Select t2.ID, t2.Value2
from t2 where t2.ID = 1


Result 2:

ID Value2
1 val5
1 val6
1 val7
1 val8


and I want to obtain like:

ID Value1 Value2
1 val1 val5
1 val2 val6
1 val3 val7
1 val4 val8


How can I obtain this result??

Answer

I have had to assume a little bit about what you want based on your results but I think the following will work:

;WITH CTE1 AS
(   SELECT  ID, Value1, ROW_NUMBER() OVER(ORDER BY Value1) [RowNumber]
    FROM    t1
), CTE2 AS
(   SELECT  ID, Value2, ROW_NUMBER() OVER(ORDER BY Value2) [RowNumber]
    FROM    t2
)
SELECT  COALESCE(CTE1.ID, CTE2.ID) [ID],
        Value1,
        Value2
FROM    CTE1
        FULL JOIN CTE2
            ON CTE1.ID = CTE2.ID
            AND CTE1.RowNumber = CTE2.RowNumber

FULL JOIN could be replaced with LEFT JOIN or INNER JOIN Depending on your exact requirements.

Comments