Join on TOP 1 of a multiple result in MS SQL

Databases
1

Hi

I have several tables that relate to a user table. The user table has a GUID column representing it’s PK (as do all the tables mentioned here).

The user table has basic info like id (see above note), fname, lname,created.

There is an address, and phone table with columns id,id_user,address1,address2,created & id,id_user,phone,created respectively.

The address & phone tables have multiple rows relating to any given user i.e a user has many addresses &/or phone numbers associated with them.

The view I am attempting to construct would return [user].id,[user].fname,[address].id,[address].address1,[phone].id,[phone].phone where the values from address & phone are the TOP 1 row as ordered by created i.e [address].created & [phone].created. If a particular user does not have an entry in either address or phone, the user record can be returned with the relative address & phone having a NULL value.

I have tried every combination of a JOIN & WHERE clause I can think of but get stuck on just returning the TOP 1 of address / phone ORDER BY created of each.


SELECT DISTINCT TOP (100) PERCENT u.id, u.fname, u.lname, a.id AS Expr1, p.id AS Expr2
FROM         cd_approp.[user] AS u LEFT OUTER JOIN
                      cd_approp.phone AS p ON u.id = p.iduser LEFT OUTER JOIN
                      cd_approp.address AS a ON u.id = a.iduser
WHERE     (a.id IN
                          (SELECT     TOP (1) id
                            FROM          cd_approp.address AS a_2
                            WHERE      (iduser = u.id))) OR
                      (a.id IS NULL) AND (p.id IN
                          (SELECT     TOP (1) id
                            FROM          cd_approp.phone AS p_2
                            WHERE      (iduser = u.id))) OR
                      (p.id IS NULL)
ORDER BY u.id

This ends up super restrictive, my other attempts are just to expansive.

Help

2

i always have to laugh when i see something like TOP (100) PERCENT :wink:


SELECT u.id
     , u.fname
     , u.lname
     , a.id AS Expr1
     , p.id AS Expr2
  FROM cd_approp.[user] AS u
LEFT OUTER
  JOIN ( SELECT iduser
              , MIN(created) AS first_created
           FROM cd_approp.phone
         GROUP
             BY iduser ) AS mp
    ON mp.iduser = u.id
LEFT OUTER
  JOIN cd_approp.phone AS p
    ON p.iduser = mp.iduser
   AND p.created = mp.first_created
LEFT OUTER
  JOIN ( SELECT iduser
              , MIN(created) AS first_created
           FROM cd_approp.address
         GROUP
             BY iduser ) AS ma
    ON ma.iduser = u.id
LEFT OUTER
  JOIN cd_approp.address AS a
    ON p.iduser = ma.iduser
   AND p.created = ma.first_created

:cool:

3

Hi Rudy

Thanks for taking the time to respond to this. Just the answer I needed.

It also spurred me into another avenue of my TSQL education.

a.M.