Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: Help with SQL query

    I've got a query that I'd like help with. Here's the situation:
    Table:
    Person
    Columns:
    PersonID, Name, AddressID

    Table:
    Address
    Columns:
    AddressID, PersonID, Address, IsPreferred

    A person can have one address or many. If a person has one address, the IsPreferred column is null. If a person has more than one address, one address will have a Y in IsPreffered, the remaining addresses will have a N.
    If I write:
    SELECT P.Name, A.AddressID, A.IsPreferred
    FROM Person P
    INNER JOIN Address A
    ON P.AddressID = A.AddressID
    I'll get:
    Name AddressID IsPreferred
    Bill 10 NULL
    Ted 20 Y
    Ted 30 N

    What I want to do is to be able to return either the preferred address if there are multiple addresses or the single address with the null in the IsPreferred column so that:
    Name AddressID IsPreferred
    Bill 10 NULL
    Ted 20 Y

    Any ideas?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Try this:
    Code:
    SELECT P.Name, A.AddressID, A.IsPreferred
      FROM Person P
     INNER JOIN Address A
        ON P.AddressID = A.AddressID
     WHERE NVL(IsPreferred,'Y') = 'Y';

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2004
    Posts
    2
    Thanks. It's a SQL Server box but replacing NVL with IsNull worked perfectly.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    You are wellcome!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    replace it with COALESCE and you got a deal!!!

    (this is the SQL forum, not the Oracle forum, not the SQL Server forum, so we should strive to use SQL, not some proprietary variant)

    also, i think the join should be on PersonID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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