> 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.
--MS_Mac_OE_3055418592_1867417_MIME_Part
Content-type: text/plain; charset="US-ASCII"
Content-transfer-encoding: 7bit
<down rover down!> grrrrrrrrr! <heal!>
You may want to look at Wrox, Professional SQL Server 7.0 Programming. I
know, not Access specific but does have a good chapter on joins which should
be much the same.
It also has a lot of other stuff that I haven't got around to reading yet,
It's red (so it goes faster than other books) and like all Wrox books
features an incredibly attractive photo of the handsome individual who wrote
it.
m
--
From: Russell Jones <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
Date: Thu, 26 Oct 2000 00:05:42 -0400
To: Fusebox <[EMAIL PROTECTED]>
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.
--MS_Mac_OE_3055418592_1867417_MIME_Part
Content-type: text/html; charset="US-ASCII"
Content-transfer-encoding: quoted-printable
<HTML>
<HEAD>
<TITLE>Re: OT: angry query with sharp teeth : avoid!!!</TITLE>
</HEAD>
<BODY>
<down rover down!> grrrrrrrrr! <heal!><BR>
<BR>
You may want to look at Wrox, Professional SQL Server 7.0 Programming. I kn=
ow, not Access specific but does have a good chapter on joins which should b=
e much the same. <BR>
<BR>
It also has a lot of other stuff that I haven't got around to reading yet, =
It's red (so it goes faster than other books) and like all Wrox books featur=
es an incredibly attractive photo of the handsome individual who wrote it.<B=
R>
<BR>
m<BR>
-- <BR>
<BR>
<BLOCKQUOTE><BR>
<B>From: </B>Russell Jones <[EMAIL PROTECTED]><BR>
<B>Reply-To: </B>[EMAIL PROTECTED]<BR>
<B>Date: </B>Thu, 26 Oct 2000 00:05:42 -0400<BR>
<B>To: </B>Fusebox <[EMAIL PROTECTED]><BR>
<B>Subject: </B>OT: angry query with sharp teeth : avoid!!!<BR>
<BR>
</BLOCKQUOTE><BR>
<BLOCKQUOTE><TT><bite!> <bite!> <Grrrrrrr!><BR>
<BR>
First thanks again to those that helped me yesterday with a similar query. =
I<BR>
figured out how to run this query on a table of messages, also pulling from=
<BR>
the users table where appropriate. (for an Access DB by the way, query<BR>
pasted below for reference) ... (The secret was a LEFT JOIN!)<BR>
<BR>
=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<BR>
<cfquery name=3D"request.getMessageTaxonomy" datasource=3D"#r=
equest.userdsn#"><BR>
SELECT m.*,u.*, m.MessageID AS ItemID, m.MessageParent A=
S ParentItemID<BR>
FROM Messages m<BR>
LEFT JOIN Users u ON m.UserID =3D =
u.UserID<BR>
WHERE &n=
bsp; m.ThreadID =3D #attributes.ThreadID#<BR>
</cfquery><BR>
=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<BR>
<BR>
Now I have this other query that works fine as is, but I need to add the<BR=
>
same sort of LEFT JOIN to it so I can pull users again where appropriate. I=
<BR>
can't figure the syntax out for the life of me. There is so little<BR>
documentation on SQL syntax for querying an access DB with Cold Fusion.<BR>
<BR>
Here is the query I'm having problems with, maybe someone could help. Mind<=
BR>
you, it works as is, but doesn't retrieve anything from the users table,<BR=
>
which I need it to do.<BR>
<BR>
=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<BR>
<cfquery name=3D"request.getThreadStart" datasource=3D"#reque=
st.userdsn#"<BR>
maxrows=3D"1"><=
BR>
SELECT *<BR>
FROM Threads t, Messages m<BR>
WHERE &n=
bsp; t.ForumID =3D #attributes.ForumID#<BR>
AND &nbs=
p; t.ThreadID =3D m.ThreadID<BR>
AND &nbs=
p; t.ThreadID =3D #attributes.ThreadID#<BR>
ORDER BY=
ThreadSubject,<BR>
&n=
bsp; MessageDateC=
reated ASC<BR>
</cfquery><BR>
=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<BR>
<BR>
My question: does anyone know how to modify this to do something like:<BR>
<BR>
=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<BR>
<cfquery name=3D"request.getThreadStart" datasource=3D"#reque=
st.userdsn#"<BR>
maxrows=3D"1"><=
BR>
SELECT t.*, m.*, u.*<BR>
FROM Threads t, Messages m<BR>
LEFT JOI=
N Users u ON m.UserID =3D u.UserID<BR>
WHERE &n=
bsp; t.ForumID =3D #attributes.ForumID#<BR>
AND &nbs=
p; t.ThreadID =3D m.ThreadID<BR>
AND &nbs=
p; t.ThreadID =3D #attributes.ThreadID#<BR>
ORDER BY=
ThreadSubject,<BR>
&n=
bsp; MessageDateC=
reated ASC<BR>
</cfquery><BR>
=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<BR>
<BR>
The query above isn't supposed to work, I just provide it as an example.<BR=
>
I've found on Allaire's discussion boards that users frequently have<BR>
problems joining more than 2 tables with a LEFT JOIN. Unfortunately, I<BR>
didn't find any examples to follow. And nowhere can I find some code to loo=
k<BR>
at to show proper syntax ... :(<BR>
<BR>
I thank you for your help.<BR>
<BR>
btw: if someone could recommend a decent in-depth website or book where I<B=
R>
could study up on querying Access on my own time, I'd appreciate it. I hate=
<BR>
to bug the group with this particular problem, but I'm not sure where else =
I<BR>
can ask. Most of the SQL sites out there never respond to my post ... or<BR=
>
don't get enough traffic to encourage response. Still waiting on the<BR>
questions I posted last week.<BR>
<BR>
Thanks.<BR>
<BR>
-Russ Jones ([EMAIL PROTECTED])<BR>
<BR>
<BR>
---------------------------------------------------------------------------=
---<BR>
To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=3Dlists&a=
mp;body=3Dlists/fusebox or send a message to [EMAIL PROTECTED]=
with 'unsubscribe' in the body.<BR>
</TT></BLOCKQUOTE><TT><BR>
</TT>
</BODY>
</HTML>
--MS_Mac_OE_3055418592_1867417_MIME_Part--
------------------------------------------------------------------------------
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.
