EasyCFM.COM ColdFusion Forums / Coding Help! / MORE SQL FUN!!!

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: MORE SQL FUN!!!

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

7R4C3
02-21-2004 @ 10:22 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 32
Joined: Jan 2004

<cfquery name="qMessageQuery" datasource="DB">
SELECT
a.id, a.formid, a.thread, a.subject, a.stamp, b.USER, b.ID
FROM message a, Contacts b
WHERE a.id = a.thread
AND a.stamp = Max(a.stamp)
order by a.date DESC
</cfquery>
ignore table b

in my table i have columns

ID THREAD MESAGE STAMP AUTHOR
1    1            
2    1
3    1
4    4
5    4
6    6

thats the pattern the new threads and replys get created.

i want to select all the unique threads, the last replyer, the number of how many replys and the last reply date of that thread. is tis possible.
i looked at all the sql commands but dont seem to get the idea.

how do i write sql for that?

can anyone help me?
???

=)

This message was edited by 7R4C3 on 2-28-04 @ 10:09 PM

7R4C3
02-25-2004 @ 1:25 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 32
Joined: Jan 2004

dooooooooooooooooooooooooooooooode
can someone help me already
pleeeeeeeeeeeeeeeeeeeeeease!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!pretty please!!!!!!!!!!

Webmaster
02-25-2004 @ 9:31 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

You are going to have to use GROUP BY (or CF GROUP) attribute.

Example:
SELECT
a.id, a.formid, a.thread, a.subject, a.date, b.USER, b.ID
FROM message a, Contacts b
WHERE a.id = a.thread
AND a.date = Max(a.date)
GROUP BY a.thread, a.id, a.formid, a.subject, a.date, b.USER, b.ID

Then that would output your data in GROUP so you can do what you need.. (Keep in mind that this is NOT tested and will prob not run.. just placed to give you example)

also you can do this:
<cfquery name="qMessageQuery" datasource="DB">
SELECT
a.id, a.formid, a.thread, a.subject, a.date, b.USER, b.ID
FROM message a, Contacts b
WHERE a.id = a.thread
AND a.date = Max(a.date)
order by a.date DESC
</cfquery>

<cfoutput query="qMessageQuery"" group="thread">
  #thread#
  <cfset counter = 0>
  <cfoutput>
    <cfset counter = counter + 1>
  </cfoutput>
  #counter#<BR>
</cfoutput>

That will show you each row and a count of items in that same thread.

Thanks,
Pablo Varando
Team Macromedia Member
=====================================================

7R4C3
02-27-2004 @ 4:59 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 32
Joined: Jan 2004

thanxxx
pablo ure the man!
noone would reply to this post,
is my table set up right,
i mean is that how u would normally design a forum table.?

7R4C3
02-28-2004 @ 9:07 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 32
Joined: Jan 2004

well, ive tryed to simplyfy this
and get everything from one table
but nothing works
i keep thinking it should be something along these lines.

<cfquery name="qMessageQuery" datasource="DB">
SELECT id, subject, thread, message, stamp, author
FROM message
group by thread
order by Max(stamp) DESC

</cfquery>

but that doesnt work,
basically it should select all the threads and group them by a thread value, then pick only the one with the latest date on it to display the most recent posts,
right?
the counter sorta works but its wrong because my sql is wrong i think.

anyone any ideas , i am totaly stuk
Question

i also changed date to stamp so there is no ACCESS conflict.

This message was edited by 7R4C3 on 2-28-04 @ 10:11 PM

7R4C3
02-28-2004 @ 9:41 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 32
Joined: Jan 2004

i tryed ure code pablo but everytime i use Max() it sais that something is not a part of an agregate function.
i am totaly lost.
and trying diferent methods just confuses me even more.

basicly it needs to select all the rows then group all the threads, select the oldest(thread) for displaying subject and author, but select the newest(thread) to display the most recent date and also order by that date.

i know i am such a theorist... but somehow i got myself into this mess. am i even doing this right????  

dk
02-29-2004 @ 12:45 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 48
Joined: Jan 2004

Get rid of the Max() and just order by the stamp field in DESC order. The most recent stamp value should be displayed anyway.


Website Designed and Developed by Pablo Varando.