Results 1 to 4 of 4

Thread: Outer Join help

  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: Outer Join help

    Ok....I have 3 tables.

    Entity
    --------
    name
    entity_key

    Address
    ----------
    street
    zip
    mailing_flag
    entity_key

    Phone
    --------
    phone_number
    phone_type_key
    entity_key


    I want to see all of the Entity records with their corresponding Address and Phone records. (select e.name, a.street, a.zip, p.phone_number)But only show the Address record for that Entity if the mailing_flag is 'Y' and I only want to see the Top 1 Phone record where the phone_type_key = 'Home'. If the above criteria isn't met I just want to see nulls for the Address and Phone records.

    My problem is getting ALL the Entity records to return. It only wants to give me the Entity records that have Address or Phone associated with them. That and somehow showing the Top 1 phone record for the Entity are my issues.

    Any help would be much appreciated......Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This sounds suspiciously like an incomplete homework assignment. Are you holding out part of the specification?

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    2
    Originally posted by Pat Phelan
    This sounds suspiciously like an incomplete homework assignment. Are you holding out part of the specification?

    -PatP
    ---Nope, that's all I need. Maybe an outer join won't work, I don't know, that's why I'm asking for help.

    Steve

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, then rolling them all together I get:
    PHP Code:
    SELECT e.*
       
    FROM dbo.entity AS e
       JOIN 
    (SELECT TOP 1 *
          
    FROM dbo.phone
          WHERE 
    'Home' phone_type_key) AS p
          ON p
    .entity_key e.entity_key)
       
    JOIN (SELECT TOP 1 a.*
          
    FROM dbo.Address
          WHERE  
    'Y' mailng_flag) AS a
          ON 
    (a.entity_key e.entity_key
    -PatP

Posting Permissions

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