Multiple Left Joins and an Inner Join

  • Thread starter Thread starter Corey Burnett
  • Start date Start date
C

Corey Burnett

I have the following tables:
Address, LState, Country, Contact

Every Contact has one or more Addresses. Each Address may
or may not have a State or Country. I want to write a
query that will pull Contact information as well as
address information. I want the query to pull State and
Country information if it is there but return NULL if not.
If I write the query without joining to the Contact table
it works fine as follows:

SELECT Address.AddressID, LState.StateName,
Country.CountryName FROM (Address LEFT JOIN LState ON
Address.StateID = LState.StateID) LEFT JOIN Country ON
Address.CountryID = Country.CountryID;

This returns the expected list of all AddressIDs with
State and Country information. Those records that do not
have associated values in the State and Country tables
have null values.

Now if I want to bring in some information about the
contact also the query changes to this:

SELECT Address.AddressID, LState.StateName,
Country.CountryName, Contact.FirstName, Contact.LastName
FROM Contact INNER JOIN ((Address LEFT JOIN LState ON
Address.StateID = LState.StateID) LEFT JOIN Country ON
Address.CountryID = Country.CountryID) ON
Contact.ContactID = Address.ContactID;

The query still runs but the problem is that when I add in
the Contact table to the query then all of a sudden the
StateName field is NULL for every record in the result
set! No matter how I rearrange the query it always omits
either the StateName or the CountryName field with NULL
values. We are using Access 2000. Any ideas?

Thanks,
Corey Burnett
 
Here is some more information about the setup:

This is an Access 2000 database that is linked via ODBC to
a SQL Server 2000 back end database. So all of the tables
are linked tables brought in from SQL Server. Also the
IDs on all of the tables in SQL Server use the
uniqueidentifier data type (don't ask me why!). I have
run in to some problems using the uniqueidentifier data
type in Access 2000 so this might be contributing to the
problem.

Just wanted to make sure that everyone had all of the
information.

Corey Burnett
 
Does anyone out there have any idea why this isn't
working? Could it have to do with the SQL
uniqueidentifier data type and Access?

Any help is appreciated.

Corey
 
Back
Top