Re: ... should be a simple query question ...
BTW; Thanks all of your awesome feedback, I solved the problem I was having. here was the correct way to add a left join in access ... well, at least it works :) -Russ == SELECT m.*,u.*, m.MessageID AS ItemID, m.MessageParent AS ParentItemID FROM Messages m LEFT JOIN Users u ON m.UserID = u.UserID WHEREm.ThreadID = #attributes.ThreadID# == > From: Russell Jones <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > Date: Mon, 23 Oct 2000 22:38:03 -0400 > To: CF-Talk <[EMAIL PROTECTED]> > Subject: ... should be a simple query question ... > > First off, I'm using Access 2000... > > I need this query to get all messages in a thread except the first message > that started it. What I have clearly does not work. I seems like it should, > but it doesn't. > > == > cachedwithin="#CreateTimeSpan(0,0,IIF(Flush,0,QueryCacheTime),0)#"> > SELECT m.*, m.MessageID AS ItemID, m.MessageParent AS ParentItemID > FROM Messages m > WHEREm.ThreadID = #attributes.ThreadID# > ANDm.MessageDateCreated >= ALL > ( > SELECT Min(m.MessageDateCreated) > FROMMessages m > WHEREm.ThreadID = #attributes.ThreadID# > ) > ORDER BY m.MessageDateCreated ASC > > == > > I know I could cut out the first record with CF when I output it, but I'd > like to do it in the query because I know it can be done. > > Are there any giving experts out there to answer my cry for help? > > Thanks. > > -Russ > > -- > -- > 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]
RE: ... should be a simple query question ...
a good web site for T SQL is available at http://www.inquiry.com/techtips/thesqlpro/ its basically a version of the SQL Server books online with a few more examples Regards Stew 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]
Re: ... should be a simple query question ...
How about changing ">=" to just ">"? -David On Tue, 24 Oct 2000 00:05:13 -0400 Russell Jones <[EMAIL PROTECTED]> writes: > Hmmm ... do you think it has something to do with > "m.MessageDateCreated >= > ALL" being a date field? I'm not sure. I can't find very goo d > documentation > onthe rules of SQL. Can someone recommend a website? > > please :( > > -Russ > > - > Subject: ... should be a simple query question ... > > First off, I'm using Access 2000... > > I need this query to get all messages in a thread except the first > message > that started it. What I have clearly does not work. I seems like it > should, > but it doesn't. > > == > datasource="#request.userdsn#" > > cachedwithin="#CreateTimeSpan(0,0,IIF(Flush,0,QueryCacheTime),0)#"> > SELECT m.*, m.MessageID AS ItemID, m.MessageParent AS > ParentItemID > FROM Messages m > WHEREm.ThreadID = #attributes.ThreadID# > ANDm.MessageDateCreated >= ALL > (SELECT Min(m.MessageDateCreated) > FROMMessages m > WHEREm.ThreadID = #attributes.ThreadID#) >ORDER BY m.MessageDateCreated ASC > > == > > I know I could cut out the first record with CF when I output it, > but I'd > like to do it in the query because I know it can be done. > > Are there any giving experts out there to answer my cry for help? > > Thanks. > > -Russ YOU'RE PAYING TOO MUCH FOR THE INTERNET! Juno now offers FREE Internet Access! Try it today - there's no risk! For your FREE software, visit: http://dl.www.juno.com/get/tagj. 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]
Re: ... should be a simple query question ...
Hmmm ... do you think it has something to do with "m.MessageDateCreated >= ALL" being a date field? I'm not sure. I can't find very goo d documentation onthe rules of SQL. Can someone recommend a website? please :( -Russ - Subject: ... should be a simple query question ... First off, I'm using Access 2000... I need this query to get all messages in a thread except the first message that started it. What I have clearly does not work. I seems like it should, but it doesn't. == SELECT m.*, m.MessageID AS ItemID, m.MessageParent AS ParentItemID FROM Messages m WHEREm.ThreadID = #attributes.ThreadID# ANDm.MessageDateCreated >= ALL (SELECT Min(m.MessageDateCreated) FROMMessages m WHEREm.ThreadID = #attributes.ThreadID#) ORDER BY m.MessageDateCreated ASC == I know I could cut out the first record with CF when I output it, but I'd like to do it in the query because I know it can be done. Are there any giving experts out there to answer my cry for help? Thanks. -Russ -- -- 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]