CODEWITHSUNDEEP
sqlselectjoin
TelJanini
TelJanini

Reputation: 835

Query question: Is there a better way to select all records from one table, along with unmatched records from another table?

Consider the following:

**Customers**
CustomerId (PK)
LastName
FirstName
Address1
City
State
Zip

**CustomerMailingAddresses**
CustomerId (PK)/(FK)
Address1
City
State
Zip

Basically, there's a one-to-one relationship between the two tables. However, not every customer record in Customers has an entry in the CustomerMailingAddresses table. I'm attempting to use T-SQL (Sql Server 2008) to generate a list of customer names and addreses. However, I only want to return the address from CustomerMailingAddresses as well as all addresses from Customers that do not have a corresponding entry for each CustomerId in CustomerMailingAddresses. In other words, the entry in CustomerMailingAddresses (if there is one), will act as an override for the address in Customers.

I've hit a wall, as none of the queries I've tried will work. I'm open to any and all suggestions.

Upvotes: 0

Views: 162

Answers (2)

hmakholm left over Monica
hmakholm left over Monica

Reputation: 23332

How about

SELECT * FROM Customers
MINUS
SELECT Customers.*
       FROM Customers, CustomerMailingAddresses
       WHERE Customers.CustomerId = CustomerMailingAddresses.CustomerId

(sorry if my SQL is a bit rusty)

Upvotes: 0

Stef Heyenrath
Stef Heyenrath

Reputation: 9820

One option is to use COALESCE 

select
    c.CustomerId,
    COALESCE(m.Address1, c.Address1) as Address1,
    COALESCE(m.City, c.City) as City,
    COALESCE(m.State, c.State) as State,
    COALESCE(m.Zip, c.Zip) as Zip
from Customers c
left join CustomerMailingAddresses m on m.CustomerId = c.CustomerId

Upvotes: 3

Related Questions