SQL LEFT JOIN to return only first matching instance

[lovallee]

Hello,
I have been searching for a way to get my expected results but with no luck so far.

Question: my LEFT JOIN operation can result more than one match but I only want the query to return the first matching instance (see sample below). Is that possible?


Table1
Field1 Field2
A D
B D
C G

Table2
Field1 Field2
D 1
D 2
G 4

Query
SELECT * FROM Table 1
LEFT JOIN Table2
On Table1.Field2 = Table2.Field1 [+ something to only select first matching instance of the LEFT JOIN]

Expected query results
Table1.Field1 Table1.Field2 Table2.Field2
A D 1
B D 1
C G 4

 

[r937]

they're called columns, not fields ;-)

SELECT tA.column1
     , tA.column2 AS tA_column2
     , tB.column2 AS tB_column2 
  FROM tA
LEFT OUTER
  JOIN ( SELECT column1
              , MIN(column2) AS min_column2
           FROM tB 
         GROUP
             BY column1 ) AS mB
    ON m.column1 = tA.column2
LEFT OUTER
  JOIN tB
    ON tB.column1 = tA.column2
   AND tB.column2 = mB.column2

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[lovallee]

R937, your are the pro!

Thank you very much!