SQL LEFT JOIN to return only first matching instance
SQL LEFT JOIN to return only first matching instance
(OP)
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
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
RE: SQL LEFT JOIN to return only first matching instance
CODE
, 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
RE: SQL LEFT JOIN to return only first matching instance
Thank you very much!