Nevyn
I asked the OP a question :
Another question. What defines the first row in Table B? Remember there is no explicit guarantee that rows return by a SELECT statement are returned in the order in which the rows were inserted.
OP's reply:
We can have first row from Table B or who's create date time stamp is first.
In response to that I added a column to Table B
CREATE Table #B(B_ID INT,A_ID INT,City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10),Entered DATETIME)
INSERT INTO #B
SELECT 1,1,'xxx','TN','37013',GETDATE() UNION ALL
SELECT 2,1,'yyy','TX','37012',GETDATE() - 40 UNION ALL
SELECT 2,2,'aaa','UT','44030', GETDATE() - 5
at this point I got lost in the detail .... Wondering how you would modify your T-SQL to select based on earliest date?
Thanks for the advice on using a RIGHt OUTER JOIN and COALESCE(rowno,1).
will test that tomorrow ...