[thelist] SQL Query problem with Left Outer Join

Pringle, Ron RPringle at aurora-il.org
Fri Mar 18 13:51:21 CST 2005


I'm using Access 2k and VBScript/ASP.

I'm querying a number of tables from the same database with the following:

SELECT * FROM (((((tbl_event EV LEFT OUTER JOIN tbl_alertDates AD ON
EV.eventID = AD.eventID) LEFT OUTER JOIN tbl_newsDates ND ON EV.eventID =
ND.eventID) LEFT OUTER JOIN tbl_eventDates ED ON EV.eventID = ED.eventID)
LEFT OUTER JOIN tbl_contact C ON EV.eventID = C.eventID) LEFT OUTER JOIN
tbl_link L ON EV.eventID = L.eventID) LEFT OUTER JOIN tbl_icon I ON
EV.eventType = I.eventType

All of the tables have an eventID field in them which is the field they are
joined on. The main table, tbl_event, contains the master record of all
events, and therefore all eventIDs, no matter if its an event, news item or
an alert. The tables tbl_contact and tbl_icon hold required information, and
so there is a record in each of them for every single eventID.

However, the tbl_link table contains optional information, so there isn't
necessarily a matching record for every eventID.

The query seems to run fine and retrieve all the records and match them on
eventID. However, when I go to retrieve the eventID for any given record,
any record that didn't have information in the tbl_link fields fails to
display an eventID.

The way I understand LEFT OUTER JOINs to work is that it will return ALL
records on the left side of the equation, and will append any results from
the right side that match the ON conditions. Is that correct?

When I do a test run of the query and display all columns retrieved, I end
up with multiple columns named eventID (one for each table in the query).
Since there are some columns in the tbl_links that are returning NULL when a
matching record isn't found, I'm thinking that is where my problem lies.
That NULL value is what is being read when I try to write out the contents
of the eventID. If that's whats happening, how exactly do I grab records
from each of these tables without also creating another eventID column every
time? Or is there something else wrong that I'm overlooking?


Regards,
Ron


More information about the thelist mailing list