SQL: Multiple joins on multiple tables where some column values are not mandatory-CodePudding

I am trying to join multiple tables where the values in columns e.Email, e.Phone are voluntary. I would like to select all rows even if e.Email, e.Phone contain empty values. Currently, I am only able to select rows where e.Email, e.Phone values are present.


SELECT a.ID_1
 
,b.Tier
 
,e.Email
 
,e.Phone



FROM CustomerActivity a 

JOIN CustomerSummary b

ON a.ID_1 = b.ID_1

JOIN DimensionCustomer c

ON b.ID_1 = c.ID_1

JOIN LegacyCustomerMapping d

ON c.ID_2 = d.ID_2

JOIN LegacyCustomerContactData e

d.ID_3 = e.ID_3

Many thanks for any kind of advice!

CodePudding user response：

I would recommend using left join instead of join in your query. That should get the results you are looking for.

Image from:

CodePudding user response：

Could you give some more information on the structure of the tables? On the first two joins you are using ID_1 while on the last two you change it to ID_2,ID_3. Seeing the tables will clear everything out.

CodePudding user response：

This can be obtained by using a LEFT OUTER JOIN for the table containing the Email and Phone fields:

SELECT a.ID_1, b.Tier, e.Email ,e.Phone
FROM CustomerActivity a 
JOIN CustomerSummary b ON a.ID_1 = b.ID_1
JOIN DimensionCustomer c ON b.ID_1 = c.ID_1
JOIN LegacyCustomerMapping d ON c.ID_2 = d.ID_2
LEFT OUTER JOIN LegacyCustomerContactData e d.ID_3 = e.ID_3

Using an OUTER JOIN avoid records not being returned when there are no related rows in the joined table. Note that OUTER is optional and might not be supported by all databases.