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

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

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

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)

Another option: OUTER APPLY

If supported by the database, OUTER APPLY is an efficient and terse option.

SELECT *
FROM 
    Table_A a
OUTER APPLY
    (SELECT TOP 1 * 
    FROM Table_B b_1
    WHERE b_1.code = a.code
    ) b
;

This results in a left join to the indeterminate first matched record. My tests show it to be quicker than any other posted solution (on MS SQL Server 2012).

If you are on SQL Server 2005 or later version, you could use [ranking][ranking] to achieve what you want. In particular, [ROW_NUMBER()][row_number] seems to suit your needs nicely:

WITH B_ranked AS (
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (PARTITION BY code ORDER BY city)
  FROM B
)
SELECT
  A.code,
  A.emp_no,
  B.city,
  B.county
FROM A
  LEFT JOIN B_ranked AS B ON A.code = B.code AND b.rnk = 1

OR

WITH B_unique_code AS (
  select * from(
     SELECT
      *,
      rnk = ROW_NUMBER() OVER (PARTITION BY code ORDER BY city)
      FROM B
     ) AS s
  where rnk = 1
)
SELECT
  A.code,
  A.emp_no,
  B.city,
  B.county
FROM A
  LEFT JOIN B_unique_code AS B ON A.code = B.code

[ranking]: http://msdn.microsoft.com/en-us/library/ms189798.aspx "Ranking Functions (Transact-SQL)" [row_number]: http://msdn.microsoft.com/en-us/library/ms186734.aspx "ROW_NUMBER (Transact-SQL)"

I modified the answer from ruakh and this seem to work perfectly with mysql.

SELECT 
   table_a.code,
   table_a.emp_no,
   table_b.city,
   table_b.county
FROM table_a a
LEFT JOIN table_b b
ON b.code = a.code 
AND b.id = (  SELECT id FROM table_b 
              WHERE table_b.code = table_a.code 
              LIMIT 1
           )
;

this is how:

 Select * From TableA a
     Left Join TableB b
         On b.Code = a.Code 
             And [Here put criteria predicate that 'defines' what the first record is]

Hey, if the city and county are unique, then use them

   Select * From TableA a
     Left Join TableB b
         On b.Code = a.Code 
             And b.City + b.county =
                  (Select Min(city + county)
                   From TableB 
                   Where Code = b.Code)

But the point is you have to put some expression in there to tell the query processor what it means to be first.

In Oracle you can do:

WITH first_b AS (SELECT code, min(rowid) AS rid FROM b GROUP BY code)) 
SELECT a.code, a.emp_no, b.city, b.county
FROM a 
INNER JOIN first_b 
 ON first_b.code = a.code
INNER JOIN b
 ON b.rowid = first_b.rid