You are trying to mix ANSI 88 and ANSI 92 SQL syntax in the following:
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
ANSI 88 (Please note the asterisk *= is left outer join)
SELECT t.*, m.*, u.*
FROM Threads t, Messages m, Users u
WHERE t.ForumID = #attributes.ForumID#
AND t.ThreadID *= m.ThreadID
AND m.UserID *= u.UserID
AND t.ThreadID = #attributes.ThreadID#
ORDER BY ThreadSubject,
MessageDateCreated ASC
ANSI 92
SELECT t.*, m.*, u.*
FROM Threads t, Messages m
LEFT OUTER JOIN Messages m on m.ThreadID = t.ThreadID -- Find
message for this thread
LEFT OUTER JOIN Users u ON m.UserID = u.UserID --
Find user for this message
WHERE t.ForumID = #attributes.ForumID#
AND t.ThreadID = #attributes.ThreadID#
ORDER BY ThreadSubject,
MessageDateCreated ASC
The above sytax retrieves all rows from Threads and only matching rows from
Messages and then matching rows from Users. You will not get any rows from
Messages and Users if there is no matching key in Threads.
Depending on the platform Left Join, Inner Join and Join can be synonymous.
You should specifically use the key word "OUTER"
Again, different platforms may interpret differently.
-Gary
-----Original Message-----
From: Russell Jones [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 26, 2000 12:06 AM
To: CF-Talk
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])
----------------------------------------------------------------------------
--------------------
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]
------------------------------------------------------------------------------------------------
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]
