SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jul 2004
    Location
    Salem, OR
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Join on TOP 1 of a multiple result in MS SQL

    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.

    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i always have to laugh when i see something like TOP (100) PERCENT
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2004
    Location
    Salem, OR
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •