helpdesk-1100431 (7/30/2010)
Hello to all,I am a having trouble in querying one-to-many relationship. let me describe the scenario here. my table structure is like this.
Table AA_IDF_NameM_NameL_Name
1ABC
2XYZ
Table B
B_IDA_IDCityStateZip
11xxxTN37013
21yyyTX37012
Now i want to select all the rows from Table A and only first respective row from Table B.
I appreciate all the help...
There may be a prettier way, but this should accomplish what you want:
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