Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unhappy Unanswered: sql statement to get the count

    hi
    i need help in writing an sql query
    there are 3 tables
    1.fc_discussions which has a column called oid (primary key)
    2.fc_discussion_threads which has columns oid(primary key), discussion_oid(foreign key to discussions oid)
    3. messages which has columns message_id (primary key),
    root_id(foreign key to fc_discussion_threads oid)

    there can be many messages associated to one fc_discussion_thread oid.

    there can be many fc_discussion_threads associated to one fc_discussions oid.

    i need to write a query that will give me the total number (as in the sum) of messages and fc_discussion threads associated to one fc_discussion oid.

    the queries i have written need to be combined to get the total for each fc_discussion oid

    select d.oid,count(dt.oid) as threadCount
    from fc_discussion_threads dt, fc_discussions d
    where dt.discussion_oid =d.oid
    group by d.oid;

    select d.oid, count(m.message_id) as messageCount
    from fc_discussions d, fc_discussion_threads dt, messages m
    where dt.discussion_oid =d.oid
    and m.root_id = dt.oid
    group by d.oid

    I really appreciate any help at all.....thanks

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: sql statement to get the count

    UNION will combine to sets of data

    so
    PHP Code:
    select d.oid,
             
    count(dt.oidCnt,
             
    'Threads' Type 
    from fc_discussion_threads dt
    fc_discussions d
    where dt
    .discussion_oid =d.oid
    group by d
    .oid
    Union
    select d
    .oid
             
    count(m.message_idCnt,
             
    'Messages' Type
    from fc_discussions d
    fc_discussion_threads dtmessages m
    where dt
    .discussion_oid =d.oid
    and m.root_id dt.oid
    group by d
    .oid 
    I added the Type column to the query so you can determine what TYPE the count is for. Sample results would be

    OID CNT TYPE
    1010 23 Threads
    1010 34 Messages

    Hope it helps.

  3. #3
    Join Date
    Oct 2003
    Posts
    2

    Smile

    thanks so much.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •