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