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

Edit
Report