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

Question

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!

you need to do an outer join. see https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join — toppk, Sep 30 '22 at 11:12

score 0 · Accepted Answer · answered Sep 30 '22 at 11:22

0

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

Image from: https://www.w3schools.com/sql/sql_join.asp

answered Sep 30 '22 at 11:22

ProdigalTechie

188
1
8

W3f..ls should know tables have rows, not records. – jarlh Sep 30 '22 at 11:42

Bouke · Answer 2 · 2022-09-30T13:29:25.687

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 avoids 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.

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

2 Answers2