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

mythix · #1 (**permalink**) 10-05-05, 09:35

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

felixg · #2 (**permalink**) 10-05-05, 10:48

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;

mythix · #3 (**permalink**) 10-06-05, 05:09

oooo, thanks for the reply. i will go and give that a go, certainly looks like the answer to all my problems

thanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech