Select first record in a One-to-Many relation using left join
left-join, sql
Solution
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
)
;
Problem
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