#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11

    showing distinct information for individual records in a grouped output


    Got another one that is giving us a headache over here:

    I have a number of queries which utilize "group by." This works great except when grabbing data from multiple queries.

    For example, I have a query "details" that grabs a bunch of related data across 5 tables and has an "order by."

    Then to get the most recent entry date:

    <CFQUERY NAME= "lastDate" DATASOURCE= "QMB_DB">select max(DATE_CREATED) date_created from qmb where qmb.pid = '#details.pid#' or qmb.qid = '#details.pid#'</CFQUERY>


    In the output:

    <CFOUTPUT QUERY= "details" group="NAME">
    <CFOUTPUT>
    <tr>
    #name#
    #lastDate.date_created#
    </tr>
    </CFOUTPUT>
    </CFOUTPUT>

    This should give me rows representing indivual query results and the most recent "date created" for the records associated with that particular row.

    Unfortunately, what it does is grab the first "created_by" date and applies it to every row.

    Any ideas?
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    You can try it without the second set of <cfoutput> tags. But regardless, won't every record displayed have the same value for lastdate.date_created because that query only returns 1 record (the max date)? You only have one value for lastdate.date_created, so you will naturally have the same value for that variable every time you output it.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11
    On the forum page here it lists various threads. For each thread it not only shows when the last entry was made in that thread, but who made that entry.

    How can you do that in ColdFusion with Oracle?
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    You would return a query with each row containing the thread information (title, thread starter, etc.) for each thread, along with the most recent time each thread was posted to. If you use the max() function, you must remember that if you mix column data and aggregate functions, you must use a GROUP BY clause in your SQL statement.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11
    I am using GROUP BY statements.

    Are you saying that each "post" record would need a data field for information on the thread? I don't have that. I thought I could run a query which compared the associated posts and then provide a seperate count for each output row, and display the most recent "date_created" for the associated records represented in each output row.

    Do I need to change the DB structure to make this work?

    At this point, the "count" displayed in each row is the count calculation for the first row only, and the "last post date" in each row is also based only on the first row calculation.
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    Yes, each record that you are displaying would have a field for the date created, something like this:

    select max(p.DATE_CREATED) lastPostDate, t.threadName
    from threads t, posts p
    where t.threadID = p.threadID
    group by t.threadName

    Now if you output each thread name, you also have the date of the most recent post for that thread. You should not need to change your database at all.
    Last edited by kiteless; March 22nd, 2004 at 10:06 AM.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11
    No threads table.

    Currently there is a post table with a post_id. Associations are established by a question_id field which is populated only in responses with the value of the post_id for the first post of the thread. So the question_id field for the second and all subsequent posts would be equal to the value of the post_id for the first post.

    So, one query identifies the questions (question_id field is null).

    Another query extracts the datafields for the records identified in the first query.

    The count query that should count the number of answers to each question just grabs for the first question only.

    The lastdate query that should grab the most recent date_created for answers to a question also only grabs the data for the first record.

    There is something we are missign here when we have a grouped output and are using multiple queries.

    If/when I find the answer I'll post it here for you. I know it can be done because I've seen it done in CF based message boards. One place even provided a demo. Unfortunately, the code was encrypted so I couldn't see how they structured the queries.
  14. #8
  15. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    Originally Posted by tech-writer
    No threads table.
    I was just following your example scenario about this forum and the way it displays threads and the date of the last post in each thread.

    I whipped up a simple access table which had a post_id, question_id, date_created, and questionText. question_id is null for new questions, and has the post_id of the original post if it is an answer to a post. This query gets all the posts, the count of replies, and the date of the most recent reply. It could probably be cleaned up (or collapsed into one query through the use of a LEFT OUTER JOIN) but I only had a few minutes:

    select q1.post_ID, q1.questionText, count(*) as postInThread, max(q2.date_created) as mostRecentPost
    from qmb q1,
    qmb q2
    where q1.question_ID IS NULL
    and q1.post_id = q2.question_id
    group by q1.post_ID, q1.questionText
    order by q1.post_ID
    UNION
    select q1.post_ID, q1.questionText, 0, date_created
    from qmb q1
    where q1.question_ID IS NULL and not exists (select 1 from qmb q2 where q1.post_id = q2.question_id)
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11
    I sent you a message ... did you get it?
  18. #10
  19. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    I did, and sent you an email with the database attached, it's a very simple setup but it should demonstrate the use of the query I posted.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11
    It was a bit tricky, but we got it implemented. Here is the complete final query:

    select qmb_user.first_name,qmb_user.last_name,qmb_user.email,
    qmb_topic_post.topic_id, qmb_post.post_id, qmb_post.post_name,
    qmb_post.post_desc, qmb_post.user_id,
    TO_CHAR(qmb_post.date_created,'MM/DD/YYYY HH24:MI') date_created,
    (select TO_CHAR( max( DATE_CREATED ) ,'MM/DD/YYYY HH24:MI' ) from QMB_POST a where a.QUESTION_id= qmb_post.post_id group by qmb_post.post_id) lastdate,
    QMB_TOPIC.topic_name, QMB_TOPIC.topic_id,
    (select count(*) from qmb_post b
    where b.question_id is not null
    and b.question_id=qmb_post.post_id group by b.question_id) anscount
    from qmb_post,
    qmb_forum, qmb_topic_post, qmb_topic, qmb_user
    where qmb_post.post_id = qmb_topic_post.post_id
    and qmb_topic_post.topic_id = qmb_topic.topic_id
    and qmb_topic.forum_id = qmb_forum.forum_id
    and qmb_forum.forum_id = '#FORUM_ID#'
    and qmb_post.question_id IS NULL
    and qmb_user.user_id = qmb_post.user_id
    order by qmb_topic_post.topic_id,qmb_post.post_id

IMN logo majestic logo threadwatch logo seochat tools logo