This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C03F02.FBFC48F0
Content-Type: text/plain;
charset="iso-8859-1"
What you really need is a course in SQL. It's got nothing to do with
querying an Access DB with Cold Fusion.
-----Original Message-----
From: Russell Jones [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 26, 2000 2:06 PM
To: Fusebox
Subject: OT: angry query with sharp teeth : avoid!!!
<bite!> <bite!> <Grrrrrrr!>
First thanks again to those that helped me yesterday with a similar query. I
figured out how to run this query on a table of messages, also pulling from
the users table where appropriate. (for an Access DB by the way, query
pasted below for reference) ... (The secret was a LEFT JOIN!)
======================-======================-======================
<cfquery name="request.getMessageTaxonomy" datasource="#request.userdsn#">
SELECT m.*,u.*, m.MessageID AS ItemID, m.MessageParent AS ParentItemID
FROM Messages m
LEFT JOIN Users u ON m.UserID = u.UserID
WHERE m.ThreadID = #attributes.ThreadID#
</cfquery>
======================-======================-======================
Now I have this other query that works fine as is, but I need to add the
same sort of LEFT JOIN to it so I can pull users again where appropriate. I
can't figure the syntax out for the life of me. There is so little
documentation on SQL syntax for querying an access DB with Cold Fusion.
Here is the query I'm having problems with, maybe someone could help. Mind
you, it works as is, but doesn't retrieve anything from the users table,
which I need it to do.
======================-======================-======================
<cfquery name="request.getThreadStart" datasource="#request.userdsn#"
maxrows="1">
SELECT *
FROM Threads t, Messages m
WHERE t.ForumID = #attributes.ForumID#
AND t.ThreadID = m.ThreadID
AND t.ThreadID = #attributes.ThreadID#
ORDER BY ThreadSubject,
MessageDateCreated ASC
</cfquery>
======================-======================-======================
My question: does anyone know how to modify this to do something like:
======================-======================-======================
<cfquery name="request.getThreadStart" datasource="#request.userdsn#"
maxrows="1">
SELECT t.*, m.*, u.*
FROM Threads t, Messages m
LEFT JOIN Users u ON m.UserID = u.UserID
WHERE t.ForumID = #attributes.ForumID#
AND t.ThreadID = m.ThreadID
AND t.ThreadID = #attributes.ThreadID#
ORDER BY ThreadSubject,
MessageDateCreated ASC
</cfquery>
======================-======================-======================
The query above isn't supposed to work, I just provide it as an example.
I've found on Allaire's discussion boards that users frequently have
problems joining more than 2 tables with a LEFT JOIN. Unfortunately, I
didn't find any examples to follow. And nowhere can I find some code to look
at to show proper syntax ... :(
I thank you for your help.
btw: if someone could recommend a decent in-depth website or book where I
could study up on querying Access on my own time, I'd appreciate it. I hate
to bug the group with this particular problem, but I'm not sure where else I
can ask. Most of the SQL sites out there never respond to my post ... or
don't get enough traffic to encourage response. Still waiting on the
questions I posted last week.
Thanks.
-Russ Jones ([EMAIL PROTECTED])
----------------------------------------------------------------------------
--
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/fusebox or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------_=_NextPart_001_01C03F02.FBFC48F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2650.12">
<TITLE>RE: angry query with sharp teeth : avoid!!!</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>What you really need is a course in SQL. It's got =
nothing to do with querying an Access DB with Cold Fusion.</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Russell Jones [<A =
HREF=3D"mailto:[EMAIL PROTECTED]">mailto:[EMAIL PROTECTED]</A>]<=
/FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, October 26, 2000 2:06 PM</FONT>
<BR><FONT SIZE=3D2>To: Fusebox</FONT>
<BR><FONT SIZE=3D2>Subject: OT: angry query with sharp teeth : =
avoid!!!</FONT>
</P>
<BR>
<P><FONT SIZE=3D2><bite!> <bite!> <Grrrrrrr!></FONT>
</P>
<P><FONT SIZE=3D2>First thanks again to those that helped me yesterday =
with a similar query. I</FONT>
<BR><FONT SIZE=3D2>figured out how to run this query on a table of =
messages, also pulling from</FONT>
<BR><FONT SIZE=3D2>the users table where appropriate. (for an Access DB =
by the way, query</FONT>
<BR><FONT SIZE=3D2>pasted below for reference) ... (The secret was a =
LEFT JOIN!)</FONT>
</P>
<P><FONT =
SIZE=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D-=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D-=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT>
<BR><FONT SIZE=3D2><cfquery =
name=3D"request.getMessageTaxonomy" =
datasource=3D"#request.userdsn#"></FONT>
<BR><FONT SIZE=3D2> SELECT m.*,u.*, m.MessageID AS =
ItemID, m.MessageParent AS ParentItemID</FONT>
<BR><FONT SIZE=3D2> FROM =
Messages m</FONT>
<BR><FONT SIZE=3D2> LEFT JOIN =
Users u ON m.UserID =3D u.UserID</FONT>
<BR><FONT =
SIZE=3D2> &nb=
sp; WHERE m.ThreadID =3D #attributes.ThreadID#</FONT>
<BR><FONT SIZE=3D2></cfquery></FONT>
<BR><FONT =
SIZE=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D-=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D-=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT>
</P>
<P><FONT SIZE=3D2>Now I have this other query that works fine as is, =
but I need to add the</FONT>
<BR><FONT SIZE=3D2>same sort of LEFT JOIN to it so I can pull users =
again where appropriate. I</FONT>
<BR><FONT SIZE=3D2>can't figure the syntax out for the life of me. =
There is so little</FONT>
<BR><FONT SIZE=3D2>documentation on SQL syntax for querying an access =
DB with Cold Fusion.</FONT>
</P>
<P><FONT SIZE=3D2>Here is the query I'm having problems with, maybe =
someone could help. Mind</FONT>
<BR><FONT SIZE=3D2>you, it works as is, but doesn't retrieve anything =
from the users table,</FONT>
<BR><FONT SIZE=3D2>which I need it to do.</FONT>
</P>
<P><FONT =
SIZE=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D-=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D-=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT>
<BR><FONT SIZE=3D2><cfquery =
name=3D"request.getThreadStart" =
datasource=3D"#request.userdsn#"</FONT>
<BR><FONT SIZE=3D2> =
maxrows=3D"1"></FONT>
<BR><FONT SIZE=3D2> SELECT *</FONT>
<BR><FONT SIZE=3D2> FROM =
Threads t, Messages m</FONT>
<BR><FONT =
SIZE=3D2> &nb=
sp; WHERE t.ForumID =3D #attributes.ForumID#</FONT>
<BR><FONT =
SIZE=3D2> &nb=
sp; AND t.ThreadID =3D =
m.ThreadID</FONT>
<BR><FONT =
SIZE=3D2> &nb=
sp; AND t.ThreadID =3D =
#attributes.ThreadID#</FONT>
<BR><FONT =
SIZE=3D2> &nb=
sp; ORDER BY ThreadSubject,</FONT>
<BR><FONT =
SIZE=3D2> &nb=
sp; &nb=
sp; MessageDateCreated ASC</FONT>
<BR><FONT SIZE=3D2></cfquery></FONT>
<BR><FONT =
SIZE=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D-=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D-=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT>
</P>
<P><FONT SIZE=3D2>My question: does anyone know how to modify this to =
do something like:</FONT>
</P>
<P><FONT =
SIZE=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D-=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D-=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT>
<BR><FONT SIZE=3D2><cfquery =
name=3D"request.getThreadStart" =
datasource=3D"#request.userdsn#"</FONT>
<BR><FONT SIZE=3D2> =
maxrows=3D"1"></FONT>
<BR><FONT SIZE=3D2> SELECT t.*, m.*, u.*</FONT>
<BR><FONT SIZE=3D2> FROM =
Threads t, Messages m</FONT>
<BR><FONT =
SIZE=3D2> &nb=
sp; LEFT JOIN Users u ON m.UserID =3D u.UserID</FONT>
<BR><FONT =
SIZE=3D2> &nb=
sp; WHERE t.ForumID =3D #attributes.ForumID#</FONT>
<BR><FONT =
SIZE=3D2> &nb=
sp; AND t.ThreadID =3D =
m.ThreadID</FONT>
<BR><FONT =
SIZE=3D2> &nb=
sp; AND t.ThreadID =3D =
#attributes.ThreadID#</FONT>
<BR><FONT =
SIZE=3D2> &nb=
sp; ORDER BY ThreadSubject,</FONT>
<BR><FONT =
SIZE=3D2> &nb=
sp; &nb=
sp; MessageDateCreated ASC</FONT>
<BR><FONT SIZE=3D2></cfquery></FONT>
<BR><FONT =
SIZE=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D-=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D-=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT>
</P>
<P><FONT SIZE=3D2>The query above isn't supposed to work, I just =
provide it as an example.</FONT>
<BR><FONT SIZE=3D2>I've found on Allaire's discussion boards that users =
frequently have</FONT>
<BR><FONT SIZE=3D2>problems joining more than 2 tables with a LEFT =
JOIN. Unfortunately, I</FONT>
<BR><FONT SIZE=3D2>didn't find any examples to follow. And nowhere can =
I find some code to look</FONT>
<BR><FONT SIZE=3D2>at to show proper syntax ... :(</FONT>
</P>
<P><FONT SIZE=3D2>I thank you for your help.</FONT>
</P>
<P><FONT SIZE=3D2>btw: if someone could recommend a decent in-depth =
website or book where I</FONT>
<BR><FONT SIZE=3D2>could study up on querying Access on my own time, =
I'd appreciate it. I hate</FONT>
<BR><FONT SIZE=3D2>to bug the group with this particular problem, but =
I'm not sure where else I</FONT>
<BR><FONT SIZE=3D2>can ask. Most of the SQL sites out there never =
respond to my post ... or</FONT>
<BR><FONT SIZE=3D2>don't get enough traffic to encourage response. =
Still waiting on the</FONT>
<BR><FONT SIZE=3D2>questions I posted last week.</FONT>
</P>
<P><FONT SIZE=3D2>Thanks.</FONT>
</P>
<P><FONT SIZE=3D2>-Russ Jones ([EMAIL PROTECTED])</FONT>
</P>
<BR>
<P><FONT =
SIZE=3D2>---------------------------------------------------------------=
---------------</FONT>
<BR><FONT SIZE=3D2>To Unsubscribe visit <A =
HREF=3D"http://www.houseoffusion.com/index.cfm?sidebar=3Dlists&body=3Dli=
sts/fusebox" =
TARGET=3D"_blank">http://www.houseoffusion.com/index.cfm?sidebar=3Dlists=
&body=3Dlists/fusebox</A> or send a message to =
[EMAIL PROTECTED] with 'unsubscribe' in the =
body.</FONT></P>
</BODY>
</HTML>
------_=_NextPart_001_01C03F02.FBFC48F0--
------------------------------------------------------------------------------
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/fusebox or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
