Left Outer Join Question/Problem

rbuser

I have created a left join successfully between two tables when there is no
'search clause' on the second table, however, when adding search criteria
to the second table unwanted results are occurring.

I have a 'patron' table and a 'patron_phone' table where different phone
types may exist for a paticular patron. I need an outer join for the
query, because I want a list of all patrons, whether or not they have a
phone number. But I also only want phone numbers where the 'phone_type' is
'DAY'.

Here is the syntax created by DADE when the SQLType is SQL2:

SELECT DISTINCT patron.patron_key,
patron.last_name,
patron.first_name,
patron_phone.phone_type_code,
patron_phone.phone
FROM patron patron
LEFT OUTER JOIN patron_phone patron_phone ON
(patron_phone.patron_key = patron.patron_key)
WHERE ( patron.patron_key BETWEEN 4001 AND 5000 )
AND ( patron_phone.phone_type_code = 'DAY' )


This query does produce a result set, but not what I am after. This is
only producing a result set for patrons that have a phone number of type
'DAY', which is more like an inner join. In other words, the qualifier of
phone_type='DAY' is being applied AFTER the join to restrict the result
set.


It would appear that for some databases (SQL Server, MySQL, ???), the
search qualifier for the second table in the join clause must be before the
'WHERE' statement in order for all patrons to be returned:

SELECT DISTINCT patron.patron_key,
patron.last_name,
patron.first_name,
patron_phone.phone_type_code,
patron_phone.phone
FROM patron patron
LEFT OUTER JOIN patron_phone patron_phone ON
(patron_phone.patron_key = patron.patron_key)
AND ( patron_phone.phone_type_code = 'DAY' )
WHERE ( patron.patron_key BETWEEN 4001 AND 5000 )


Moving the qualifier for phone_type='DAY' to an area above the WHERE clause
produces the desired result but requires that an end-user edit the
resulting query. This has a number of unwanted side effects, especially
that of negating any autosearch criteria.

Are there any plans to support this form of outer-join syntax?

digitalmetaphors

Yes, criteria on a join expression is on our requested features list. The
current solution is to edit the SQL text. You're right, when the criteria is
placed in the WHERE clause instead of the join, it effectively creates an
inner join.


Cheers,

Jim Bennett
Digital Metaphors