Select first record in a One-to-Many relation using left join

Solution 1:

After playing around a bit, this turns out to be trickier than I'd expected! Assuming that table_b has some single column that is unique (say, a single-field primary key), it looks like you can do this:

SELECT table_a.code,
       table_a.emp_no,
       table_b.city,
       table_b.county
  FROM table_a
  LEFT
  JOIN table_b
    ON table_b.code = table_a.code
   AND table_b.field_that_is_unique =
        ( SELECT TOP 1
                 field_that_is_unique
            FROM table_b
           WHERE table_b.code = table_a.code
       )
;

Solution 2:

The highest voted answer does not seem correct to me, and seems overcomplicated. Just group by the code field on table B in your subquery and select the maximum Id per grouping.

SELECT 
    table_a.code,
    table_a.emp_no,
    table_b.city,
    table_b.county
FROM 
    table_a
    LEFT JOIN 
        table_b
        ON table_b.code = table_a.code
        AND table_b.field_that_is_unique IN
            (SELECT MAX(field_that_is_unique)
             FROM table_b
             GROUP BY table_b.code)