Ok, I have never done this with RIGHT JOINs, just LEFT JOINs. The trick to
making it work is you put () around one the joins and treat that as if it is
one table and join that to the third table. This will not be right for your
case, but let me do a SQL statement with the three tables below as a LEFT
JOIN totally.
SELECT FixedTeeTimes.FixedTeeTimes, Calendar.CustomerID,
Customers.CustomerID, Customers.ContactFirstName, Customers.ContactLastName,
Customers.PhoneNumber, Customers.EmailAddress
FROM (Calendar LEFT OUTER JOIN FixedTeeTimes ON Calendar.TeeID =
FixedTeeTimes.TeeID) LEFT OUTER JOIN Customers ON Customers.CustomerID =
Calendar.CustomerID
Ok, this one should work, bringing up all the Calendar results and only
those associated FixedTeeTimes and Customers. Maybe you want to bring up
all the customers and their associatioed FixedTeeTimes and Calendar results,
well that would look like this with LEFT JOINS:
SELECT FixedTeeTimes.FixedTeeTimes, Calendar.CustomerID,
Customers.CustomerID, Customers.ContactFirstName, Customers.ContactLastName,
Customers.PhoneNumber, Customers.EmailAddress
FROM (Customers LEFT OUTER JOIN Calendar ON Customers.CustomerID =
Calendar.CustomerID) LEFT OUTER JOIN FixedTeeTimes ON FixedTeeTimes.TeeID =
Calendar.TeeID
What the trick is that there is () around one of the joins and then you
treat that as a single table that has all the columns of the first two
tables and join it on a third. I have not tried with RIGHT JOINs yet, and I
am having a lot of trouble figuring out how to order it to work with RIGHT
JOINs, but I think the LEFT JOIN SQL statement I just gave above should
accomplish what you want, if I am guessing right from your SQL statement.
Hope this helps you to understand joining multiple tables better...
Justin Kidman
-----Original Message-----
From: Mark Adams [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 06, 2000 7:13 PM
To: [EMAIL PROTECTED]
Subject: Re: OUTER JOIN w/more than 2 tables
Hey, Justin
This is wierd I post a message it takes about 3 to 4 hours before its
posted. Is that normal for this list?
Anyway I thought it would be more productive to just email you direct I'm
still not getting anywhere with this OUTER JOIN thing as I said before I
have successfully done these with 2 tables no problem, but when I add a 3rd
table then there's a problem. MS Access says to create two queries then
refer to the 1st query with the 2nd this does work but it seems like there
is a better way.
<!-----Here is the code you sent----->
SELECT FixedTeeTimes.FixedTeeTimes, Calendar.CustomerID,
Customers.CustomerID, Customers.ContactFirstName, Customers.ContactLastName,
Customers.PhoneNumber, Customers.EmailAddress
FROM (oj Calendar RIGHT OUTER JOIN FixedTeeTimes ON
Calendar.TeeID = FixedTeeTimes.TeeID) RIGHT OUTER JOIN Customers ON
Customers.CustomerID = Calendar.CustomerID
<!-----Here is the code you sent----->
Here is the latest error,
<!-----Error Message----->
Error Diagnostic Information
ODBC Error Code = S1000 (General error)
[Microsoft][ODBC Microsoft Access Driver] Join expression not supported.
I'm just not getting this stuff. It seems so easy yet its irritating the
h#$%@l out of me. AHHH!
HELP!
-Mark
----- Original Message -----
From: Justin Kidman <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, September 06, 2000 1:05 PM
Subject: RE: OUTER JOIN w/more than 2 tables
> Hmm, me not cutting out enough things... Bah, no more copy paste on other
> ppl's code... =P
>
> Justin Kidman
>
> -----Original Message-----
> From: Jeremy Allen [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, September 06, 2000 12:01 PM
> To: [EMAIL PROTECTED]
> Subject: RE: OUTER JOIN w/more than 2 tables
>
>
> What is the "oj" right after the FROM?
>
> Shouldnt that not be there..? Or is oj a
> table and your aliasing it to Calendar??
>
> Jeremy
>
> -----Original Message-----
> From: Mark Adams [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, September 06, 2000 2:17 PM
> To: [EMAIL PROTECTED]
> Subject: Re: OUTER JOIN w/more than 2 tables
>
>
> I tried it and I keep getting this error. Its the same error I was getting
> before. I must be missing something.
>
>
>
> <!-----Error Message----->
> ODBC Error Code = 37000 (Syntax error or access violation)
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation.
>
> -Mark
>
>
>
> ----- Original Message -----
> From: Justin Kidman <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, September 06, 2000 10:27 AM
> Subject: RE: OUTER JOIN w/more than 2 tables
>
>
> > Do this all the time, n-table joins, just need to use () well.
> >
> > SELECT FixedTeeTimes.FixedTeeTimes, Calendar.CustomerID,
> > Customers.CustomerID, Customers.ContactFirstName,
> Customers.ContactLastName,
> > Customers.PhoneNumber, Customers.EmailAddress
> > FROM oj Calendar RIGHT OUTER JOIN (FixedTeeTimes ON
> > Calendar.TeeID = FixedTeeTimes.TeeID RIGHT OUTER JOIN Customers) ON
> > Customers.CustomerID = Calendar.CustomerID
> >
> > Use the () to left if you are using a LEFT OUTER JOIN.
> >
> > Justin Kidman
> >
> > -----Original Message-----
> > From: Mark Adams [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, September 06, 2000 10:10 AM
> > To: [EMAIL PROTECTED]
> > Subject: OUTER JOIN w/more than 2 tables
> >
> >
> > Hello All!
> >
> > "OUTER JOIN" with 3 tables? I have tried this many ways and I keep
getting
> > errors. If I set up a query with 3 or more tables everything works fine
> > until I change the "INNER JOIN" to an "OUTER JOIN" So I tried setting up
> > just 2 tables with an "OUTER JOIN" everything works great but if I add
> > another table it errors again. AHHHH!
> >
> > The queries are below.
> >
> > What am I missing?
> >
> > Any help is appreciated.
> >
> > Mark :o)
> >
> >
> >
> > <!-----This one works fine----->
> > SELECT FixedTeeTimes.FixedTeeTimes, Calendar.CustomerID
> > FROM {oj Calendar RIGHT OUTER JOIN FixedTeeTimes ON
Calendar.TeeID
> =
> > FixedTeeTimes.TeeID }
> >
> > <!-----But I need data from 3rd table so I add this and it errors
> out----->
> > SELECT FixedTeeTimes.Fixed
> TeeTimes, Calendar.CustomerID,
> > Customers.CustomerID, Customers.ContactFirstName,
> Customers.ContactLastName,
> > Customers.PhoneNumber, Customers.EmailAddress
> > FROM Customers, {oj Calendar RIGHT OUTER JOIN FixedTeeTimes ON
> > Calendar.TeeID = FixedTeeTimes.TeeID }
> > WHERE Customers.CustomerID = Calendar.CustomerID
> >
> >
> >
> >
> >
>
> --------------------------------------------------------------------------
> --
> > --
> > Archives: http://www.mail-archive.com/[email protected]/
> > To Unsubscribe visit
> > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
or
> > send a message to [EMAIL PROTECTED] with 'unsubscribe'
in
> > the body.
>
> --------------------------------------------------------------------------
> ----
> > Archives: http://www.mail-archive.com/[email protected]/
> > To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
> >
>
> --------------------------------------------------------------------------
--
> --
> Archives: http://www.mail-archive.com/[email protected]/
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
>
> --------------------------------------------------------------------------
--
> --
> Archives: http://www.mail-archive.com/[email protected]/
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
> --------------------------------------------------------------------------
----
> Archives: http://www.mail-archive.com/[email protected]/
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
>
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
