Thanks, Justin
It worked perfect just had to create a couple of <cfif> <cfelse> statements
for the NULL Date fields and whala!
Thank you! :o)
-Mark
----- Original Message -----
From: Justin Kidman <[EMAIL PROTECTED]>
To: 'Mark Adams' <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, September 07, 2000 9:03 AM
Subject: RE: OUTER JOIN w/more than 2 tables
> 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.
>
------------------------------------------------------------------------------
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.
