SQL LEFT JOIN to return only first matching instance

lovallee · Sep 22, 2009

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 · Sep 22, 2009

they're called columns, not fields ;-)

Code:

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 · Sep 23, 2009

R937, your are the pro!

Thank you very much!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQL LEFT JOIN to return only first matching instance

lovallee

Technical User

r937

Technical User

lovallee

Technical User

Similar threads

Part and Inventory Search

Sponsor