One record selected over 1:m relationship.

Question

Two simple tables:

T1

ContactId, Name, Department

T2

AddressId OwnerId Address Added

What I need to output is: ContactId, Name, Department, Address however I only want one record per cusomer, the oldest based on Added (datetime).

Is this possible?

Answer 1

Yes it is possible.

You'll have to use a correlated subquery:

select contactid, name, department, address
from t1
inner join t2 x on t1.contactid = x.ownerid
where x.added = ( select max(t2.added) from t2 where t2.ownerid = x.ownerid )

Use an outer join when you want to return every contact:

select contactid, name, department, address
from t1
outer join t2 x on t1.contactid = x.ownerid
where x.added = ( select max(t2.added) from t2 where t2.ownerid = x.ownerid )

Answer 2

You can do it as follows:

SELECT T1.contactId,T1.name,T2.department,T2.address FROM T1 INNER JOIN T2  ON T1.ContactId=T2.OwnerId INNER JOIN (SELECT OwnerId,MAX(Added) FROM T2 GROUP BY OwnerId) LatestAddress ON  T2.OwnerId=LatestAddress.OwnerId

Answer 3

Re-work of Frederik's solution, but should be faster if AddressID is your PK (clustered). Also use LEFT JOIN in case there are no addresses for the Contact. If one is sure to have one, then replace with INNER JOIN:

SELECT      t1.contactid, 
            t1.name, 
            t1.department, 
            t2.address
FROM        t1
LEFT JOIN   t2
        ON  t2.addressid = (SELECT  TOP 1 x.addressid 
                            FROM    t2 x 
                            WHERE   x.ownerid = t1.contactid
                            ORDER BY t2.added ASC)

Are you sure you want the oldest? Change ASC to DESC if you would like the newest.