bitbucket-25253 (7/31/2010)
Nevyn changed my create table statements to use your table names and ran your code:
SELECT *FROM Table_A a
LEFT OUTER JOIN
(SELECT B_ID,A_ID,City,State,Zip
FROM Table_B b
INNER JOIN
(SELECT A_ID,bid=(min B_ID)
FROM Table_B
GROUP BY A_ID) as inner
ON inner.bid=b.B_ID) as outer
ON a.A_ID=outer.A_ID
Result: Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'B_ID'.
Wondering what I did incorrectly?
You didn't, I did.
I didnt test it or debug (wasnt at a sql server machine). Had some resrved words for alias names and incorrect brackets. Try the below
CREATE Table #Table_A (A_ID INT,F_Name VARCHAR(2), M_Name VARCHAR(2),L_Name1 VARCHAR(2))
INSERT INTO #Table_A
SELECT 1,'A','B','C'UNION ALL
SELECT 2,'X','Y','Z'
CREATE Table #Table_B(B_ID INT,A_ID INT, City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10))
INSERT INTO #Table_B
SELECT 1,1,'xxx','TN','37013' UNION ALL
SELECT 2,1,'yyy','TX','37012'
SELECT *
FROM #Table_A a
LEFT OUTER JOIN
(SELECT B_ID,b.A_ID,City,State,Zip
FROM #Table_B b
INNER JOIN
(SELECT A_ID,bid=min (B_ID)
FROM #Table_B
GROUP BY A_ID) as derived2
ON derived2.bid=b.B_ID) as derived1
ON a.A_ID=derived1.A_ID
DROP TABLE #Table_A
DROP TABLE #Table_B
My only comment on your solution is that it won't show a ROW of A if there isnt a matching row in B. Can be fixed by making the join right outer and putting the where condition on COALESCE(rowno,1).
I should also add that mine doesnt work with your amended test data because you B_ID wasnt unique in it