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

Question

I'm trying to join two tables using a left-join. And the result set has to include only the first record from the "right" joined table.

Lets say I have two tables A and B as below;

Table "A"

code | emp_no  101  | 12222 102  | 23333 103  | 34444 104  | 45555 105  | 56666 

Table "B"

code | city       | county 101  | Glen Oaks  | Queens 101  | Astoria    | Queens 101  | Flushing   | Queens 102  | Ridgewood  | Brooklyn 103  | Bayside    | New York 

Expected Output:

code | emp_no | city      | county 101  | 12222  | Glen Oaks | Queens 102  | 23333  | Ridgewood | Brooklyn 103  | 34444  | Bayside   | New York 104  | 45555  | NULL      | NULL 105  | 56666  | NULL      | NULL 

If you notice my result has only the one matched record from table "B"(doesn't matter what record is matched) after left join (and it is a one to many mapping)

I need to pick the first matched record from table B and ignore all other rows.

Please help!

Thanks

Answer 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        ) ; 

Answer 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)