not including null values in sql join

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    
10开发者_运维知识库1, 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.

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.

Give this a go

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

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

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.

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