Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Posts
    63

    Unanswered: a ticket with more than one post (was "Simple SQL question")

    First, thanks for looking

    Second here is the monstrosity of the problem grrrr.

    there are three tables:

    jos_wats_tickets
    jos_wats_msg
    jos_user

    A user can submit a new ticket with a message (note there is NEVER a ticket without a message)

    Then any user can submit a reply message.

    I want to get the following out of the tables:

    number of messages in a ticket
    ticket id
    ticket name
    ticket submit date and time
    ticket user id -> username from jos_users
    first message id
    last message id
    last message date and time
    last message user id -> username from jos_users

    This is what I have so far:

    Code:
    SELECT
        COUNT (*) AS replies,
        t.ticketid,
        t.watsid AS ownerid, 
        t.ticketname,
        t.datetime AS startdate,
        m1.msgid AS firstmsg,
        m2.msgid AS lastmsg,
        m2.watsid AS lastid,
        m2.datetime AS lastdate,
        o.username AS ownername,
        p.username AS postername
    FROM
        (
            (
                (
                jos_wats_ticket AS t LEFT JOIN jos_wats_msg AS m1 ON
                    (
                    t.ticketid = m1.ticketid
                    )
                )
            LEFT JOIN jos_wats_msg AS m2 ON
                (
                t.ticketid = m2.ticketid
                )
            )
        LEFT JOIN jos_users AS o ON
            (
            t.watsid = o.id
            )
        )
    LEFT JOIN jos_users AS p ON
        (
        m2.watsid = p.id
        )
    WHERE (m1.datetime <= m2.datetime)
    GROUP BY t.ticketid, m2.msgid
    ORDER BY t.ticketid
    The trouble is that if there is a ticket with more than one post I get one row showing it with one post another showing it with 2 posts etc... Is there a way to overcome this, or do I need to process the results after I have executed the SQL? Note that I do not want to use subqueries because of compatability issues with earlier versions of MySQL.

    thanks

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by mythix
    The trouble is that if there is a ticket with more than one post I get one row showing it with one post another showing it with 2 posts etc... Is there a way to overcome this, or do I need to process the results after I have executed the SQL?
    Note that I do not want to use subqueries because of compatability issues with earlier versions of MySQL.
    Using the MAX-CONCAT trick (see http://dev.mysql.com/doc/mysql/en/ex...group-row.html)
    since you can't do subqueries.

    Is this what you want?
    Code:
    SELECT
        COUNT(*) AS replies,
        t.ticketid,
        t.watsid AS ownerid, 
        t.ticketname,
        t.datetime AS startdate,
        SUBSTRING(MIN(CONCAT(m1.datetime, m1.msgid)), 20) as firstmsg,
        SUBSTRING(MAX(CONCAT(m1.datetime, m1.msgid)), 20) as lastmsg,
        SUBSTRING(MAX(CONCAT(m1.datetime, m1.watsid)), 20) as lastid,
        MAX(m1.datetime) as lastdate,
        o.username AS ownername,
        SUBSTRING(MAX(CONCAT(m1.datetime, p.username)), 20) AS postername
    FROM jos_wats_ticket AS t 
    LEFT JOIN jos_wats_msg AS m1 ON t.ticketid = m1.ticketid
    LEFT JOIN jos_users AS o ON t.watsid = o.id
    LEFT JOIN jos_users AS p ON m1.watsid = p.id
    GROUP BY t.ticketid, t.watsid, t.ticketname, t.datetime
    ORDER BY t.ticketid;

  3. #3
    Join Date
    Jun 2002
    Posts
    63
    oooo, thanks for the reply. i will go and give that a go, certainly looks like the answer to all my problems thanks.

Posting Permissions

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