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?

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';

Thanks. It's a SQL Server box but replacing NVL with IsNull worked perfectly.

You are wellcome!

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