not including null values in sql join

Question

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

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.