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 am 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

This seems like a simple connection, but I am having problems with the above result set. Please, help.

Thank.

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.

Answer 2

WHERE, , .

Answer 3

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

Answer 4

It looks like a left join with me.

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

This way you get all the address records, and if someone does not have the corresponding CustomerTransaction, you should get an empty string.

Answer 5

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

with a clear you won’t get twice a carmen.