Columns in outer joins

In evaluating gui SQL reporting tools, it seems common to assist in the outer joining of tables but ignore outer joining column references in the outer joined table.

For example, when joining an address table and phone table, the table join is built correctly, as:

        address_studentid = phone_studentid (+)

This will correctly pull students with or without phones.

        However, when there is a phone, we usually only want to pull the main phone number and so add a statement like:

        phone_type = 'Main'

Unless this line is manually edited to

        phone_type (+) = 'Main'
the table outer join is useless and only pulls students with phones, specifically of the type 'Main' - and therefore negates having outer joined the tables in the first place.

When I've queried the software vendors, they seem to shrug and say "That's the way SQL works."

Fundamentally, this makes no sense, since I cannot imagine an instance where after having outer joined tables that I would NOT want

         phone_type (+) = 'Main'
so I don't understand why any vendor would choose to implement the behavior of

         phone_type = 'Main'
and claim it's the user's problem to outer join the phone_type column.

Any thoughts?

Thanks,
Stuart Kohler
<skohler_at_norwich.edu>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Aug 27 1999 - 13:39:45 CDT