CODEWITHSUNDEEP
sqlsql-serversql-server-2008joinduplicates
user1802898
user1802898

Reputation: 23

Join Tables SQL Server with duplicates

I have a table 

col1

1
2

and other table 

col1    col2    col3

1          1    data value one                                    
1          2    data value one                                    
2          3    data value two

and I want to join both tables to obtain the following result

col1  col2  col3

1       1    data value one
2       3    data value two

The second table have duplicates but I need to join only one (randomly). I've tried with Inner Join, Left Join, Right Join and always returns all rows. Actually I use SQL Server 2008.

Upvotes: 2

Views: 206

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44336

select t1.col1, t2.col2, t2.col3 from table1 t1
cross apply
(select top 1 col2, col3 from table2 where col1 = t1.col1 order by newid()) t2

Upvotes: 3

GarethD
GarethD

Reputation: 69819

You can use the ROW_NUMBER Function along with ORDER BY NEWID() To get one random row for each value in col1:

WITH CTE AS
(   SELECT  Col1,
            Col2,
            Col3, 
            [RowNumber] = ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY NEWID())
    FROM    Table2
)
SELECT  *
FROM    Table1
        INNER JOIN CTE
            ON CTE.Col1 = table1.Col1
            AND CTE.RowNumber = 1 -- ONLY GET ONE ROW FOR EACH VALUE

Upvotes: 0

indiPy
indiPy

Reputation: 8072

Use Distinct it will eliminate dups, but you sure both rows will contain same data?

Upvotes: 0

Related Questions