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??

Answers


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.


Need Your Help

Syntax highlighting for *.sbt files in IntelliJ IDEA

scala intellij-idea syntax-highlighting sbt sbt-idea

I'm working on a potential module for the Lift Web framework using Scala, SBT 0.12, and IntelliJ IDEA 12.1, and I would like to have syntax highlighting for *.sbt files in IDEA (code awareness woul...

Access/extract R help from non-R script (e.g. Python)

python r

I found a number of functions here on stackoverflow that extract the html code of help files from within R but I would like to access the help files from an external script (Python). How can I get ...