query with multiple outer joins gives wrong result

[Jurjen]

Hello,

My query references 2 related tables: one for persons (PERS) and one for telephone/fax numbers and email addresses (CONT, for Contacts).
The relevant fields are:

PERS
pers_id (long) ---> primary key
pers_forename (text)
pers_surname (text)

CONT
cont_id (long) ---> primary key
pers_id (long) ---> foreign key
come_id (long) ---> foreign key
cont_number (text)

The foreign key come_id refers to a table for contact methods (COME), either "Phone (Home)", "Phone (Work)", "Mobile", "Fax" or "Email".

Now i want to list all persons with their home phone number and email address, also if they don't have one. It seems to be impossible to get it ...
I will explain what happens. Lets start simple: first list all persons with their home phone number (come_id = 1):

SELECT PERS.pers_forename, PERS.pers_surname, CONT.cont_number AS Phone
FROM PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id
WHERE (((IIf(IsNull([come_id]),1,[come_id]))=1));

This works fine. The IIf expression is necessary since we are dealing with an outer join: not all persons have a home phone number. If we would simply put "WHERE come_id = 1" then the query produces only the persons that have a home phone number.

But now i also want to see the email address (come_id = 5):

SELECT PERS.pers_forename, PERS.pers_surname, 
       CONT.cont_number AS Phone, CONT_1.cont_number AS Email
FROM CONT AS CONT_1 RIGHT JOIN 
     (PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id) 
     ON CONT_1.pers_id = PERS.pers_id
WHERE (((IIf(IsNull([cont].[come_id]),1,[cont].[come_id]))=1) 
  AND ((IIf(IsNull([cont_1].[come_id]),5,[cont_1].[come_id]))=5));

It seems perfectly logical: i added a second alias CONT_1 for the email address. Since this is also optional we have a second outer join, and the WHERE condition should also use an IIf expression.
The result is not correct though: the resulting recordset shows only the persons that have both a home phone number and an email addres or neither!
I have a lot of experience with SQL and queries, but i know i am not infallible. Nevertheless i am quite convinced that i should get all the persons: those that have a home phone number or an email address, or both or neither ...
I hope that someone of you can explain this.

 

[Rabbie]

I would advise you to initially break this down in to a series of sub tasks

1) a query to list all those with home number
2) a query to list all those with email address
3) a query to list all those without home number
4) a a query to list all those without email address

Then you can use a union query to combine these results

Hope this helps

 

[Jurjen]

multiple outer joins: problem solved!

Dear Rabbie,

thank you for your answer. I did not try your solution, because i have solved the problem myself. It seems to be shorter than what you suggest, and i do not need a union query. Indeed we have to break the query down into sub tasks:

A query that lists all home phone numbers (qry_cont_home):

SELECT CONT.pers_id, CONT.cont_number
FROM CONT WHERE (((CONT.come_id)=1));

A query that lists all email addresses (qry_cont_email):

SELECT CONT.pers_id, CONT.cont_number
FROM CONT WHERE (((CONT.come_id)=5));

And finally the main query that combines these two:

SELECT PERS.pers_forename, PERS.pers_surname,
    qry_cont_home.cont_number, qry_cont_email.cont_number
FROM (PERS LEFT JOIN qry_cont_home ON PERS.pers_id = qry_cont_home.pers_id)
LEFT JOIN qry_cont_email ON PERS.pers_id = qry_cont_email.pers_id;

It seems that separating the multiple outer joins from the complex WHERE clause did the trick ...

 

[Rabbie]

Glad to hear you have got it working. It is always better if you can solve it yourself because then you will be able to change it in the future if required.