开发者

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.


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


How about

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

(sorry if my SQL is a bit rusty)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜