Join two tables and return only one row from second table

Question

I have two tables. Both tables contains an ID that can be used to join them together. However, table 2 can have more than one row for the same ID. Table 1 will always have one row.

For instance, table 1 contains 60,000 rows. When I do a left inner join with table2 it returns ~171 000 rows.

What I'm trying to accomplish is return the first row from table 2 so when I join them I do not create a duplicate records.

I'm not sure if this is the correct approach but this is what I tried:

SELECT I.*, a.Name from table 1 I 
INNER JOIN (SELECT MIN (a.ID), a.NAME FROM table 2 group by a.ID) as a 
on I.ID = a.ID

I get the following error:

Incorrect syntax near 'i'.

Any suggestions on how I can fix this? Or get the result in a different way?

Answer 1

Something like this will guarantee one row per id from your second table.

;WITH singleRows AS (
SELECT
    *
    , ROW_NUMBER() OVER (PARTITION BY id ORDER BY name) as rownum
FROM
    table_2)

SELECT
    *
FROM
    table_1 t1

    JOIN singleRows t2
        ON t1.id = t2.id
        AND t2.rownum = 1

Answer 2

Make sure the second table has an ID column and joining column with T1 say RelationId. Then you can do:

SELECT T1.*, T2.*
FROM Table1 T1 
INNER JOIN Table2 T2 ON T2.RelationID = T1.RelationId AND T2.ID = (SELECT MIN(ID) FROM Table2 WHERE RelationId = T1.RelationId)