<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])
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
