not including null values in sql join

Question

Hi, I have two tables CustomerAddress(CustomerId, City, Country) and CustomerTransactions(TransactionId, CustomerId, CustomerContact). Here are the values in the tables:

For CustomerAddress:

1001, El Paso, USA    
1002, Paris, France    
1003, Essen, Germany    

For CustomerTransactions:

98, 1001, Phillip    
99, 1001, NULL
100, 1001, NULL    
101, 1003, Carmen    
102, 1003, Carmen    
103, 1003, Lola    
104, 1003, NULL    
105, 1002, NULL

I'm trying to join both tables and have the following result set:

1001, El Paso, USA, Phillip    
1002, Paris, France, (empty string)    
1003, Essen, Germany, Carmen    
1003, Essen, Germany, Lola

It seems like a simple join but I'm having trouble coming up with the above result set. Please help.

Thanks.

Answer 1

Just add a WHERE clause that ensures the column is not null.

Answer 2

Give this a go

SELECT *
FROM CustomerAddress ca
INNER JOIN CustomerTransactions ct
    ON ca.CustomerId = ct.CustomerId
GROUP BY ct.CustomerId, ct.CustomerContact

Answer 3

This looks like a left join to me.

select ca.CustomerAddressID, ca.City, ca.Country, ISNULL(ct.CustomerContact, '')
from CustomerAddress ca
left join CustomerTransaction ct on ca.CustomerID = ct.CustomerID

That way you get all the address records, and if any don't have a corresponding CustomerTransaction you should get an empty string.

Answer 4

select distinct 
   ca.CustomerAddressID
  ,ca.City
  ,ca.Country
  ,ct.CustomerContact
from CustomerAddress ca
left join CustomerTransaction ct on ca.CustomerID = ct.CustomerID

with distinct you will not get carmen twice

Answer 5

I finally figured it out...

SELECT DISTINCT CA.CustomerId, CA.CustomerCity, CA.CustomerCountry, ISNULL(CT.CustomerContact) AS CustomerContact
FROM CustomerAddress CA
LEFT JOIN (SELECT CustomerId, CustomerContact 
           FROM CustomerTransactions
           WHERE CustomerContact IS NOT NULL) CT ON CT.CustomerID = CA.CustomerID

Thanks for putting me on the right track.