Utilising Left Outer Join correctly

Question

I have 3 tables:

Table one has a list of contacts Table 2 has a list of distribution lists Table 3 has a list of contacts associated with each of the distribution lists.

I am trying to get a list of all Contacts under a particular scheme and include any distribution lists they are in, and still return the contact if they are not in any distribution list.

so

Table 1 : Contacts

ID	Firstname	Surname	ServiceID---	Email-------	Phone
1	Joe	Blogs	1136	[email protected]	xxxxx
2	Sue	Bloggs	1136	[email protected]	xxxxx
3	Jamie	Howard	1146	Xxxxx	xxxxx
4	Joseph	Stalin	1146	Xxxxx	xxxxx
5	Helen	Blower	1136	Xxxxx	xxxxx
6	Mike	Grys	1136	Xxxxx	xxxxx
7	Rose	Flower	1146	Xxxxx	xxxxx
8	John	Prior	1136		xxxxx

Table 2: Distribution lists

Distlistid	distlistname
1	Red Zone
2	Blue Zone
3	Yellow Zone
4	Pink Zone
5	Green Zone

Table 3 : Distribution list Contacts

Distlistid	Contact id
1	1
1	2
1	3
2	2
2	4
2	7
2	8
3	1
3	7
4	1
4	2
4	4
4	7
4	8
5	1
5	2
5	3
5	5
5	7
5	8

Script I am using is:

SELECT c.firstName,
       c.lastName,
       c.comments,
       dl.distListName,
       dl.description
FROM app.Contact c WITH (NOLOCK)
     RIGHT OUTER JOIN app.DistListContacts DC ON c.id = DC.contactID
     JOIN app.DistList dl ON DC.distListID = dl.id
WHERE c.serviceID = 1136
GROUP BY dl.distListName,
         c.firstName,
         c.lastName,
         c.comments,
         dl.description
ORDER BY c.lastName,
         c.firstName;

So I want a list of all contacts under serviceid 1136 and what distribution list they are a member of if any.

I have tried left outer join but I only seem to get the contacts that have a distribution list, not the ones that dont.

Probably a very simple mistake I am making?

Answer 1

You should use LEFT JOIN Here is a simple query that will give you your desired results. I have removed columns "description" and "comments" because I do not know where did you got them from.

select c.firstName,
       c.lastName,
       dl.distListName,
       c.serviceID
from Contact c
left JOIN DistListContacts DC ON c.id = DC.contactID
left JOIN DistList dl ON DC.distListID = dl.id
WHERE c.serviceID = 1136
ORDER BY c.lastName,
         c.firstName;

Here you can see the DEMO