Query Help

[arianhojat]

Hello all,

need help for a Query if anyone has a lil time:

 

There are 2 tables for our help desk system, summarized below:

 

requestdescriptions of IT support requests

requestIDDescription

1XP got the Blue Screen of Death.

2Keyboard not working.

3Needs photoshop installed.

4Computer not starting.

 

 

routingwho request is routed to and if they closed it

requestIDEmployee_IDDateGotTheRequestDatePassedOnTheRequestSolvedItWhatYouDid

1122006-3-1 12:01:002006-3-1 14:47:000Couldnt solve it, might be hardware issue.

1972006-3-1 14:47:002006-3-2 10:58:000not hardware, passing back to another software guy

1462006-3-2 10:58:002006-3-2 11:34:001Had bad drivers, reinstalled XP

2462006-3-1 7:58:002006-3-5 15:58:000not sure, working on this past few days... passing this to Dave

2972006-3-5 15:58:002006-3-5 17:17:001keyboard not plugged in

3122006-3-12 9:23:002006-3-12 10:24:001put order in for photoshop

 

4122006-3-17 15:23:002006-3-18 12:24:000checked power supply and motherboard, not sure

4462006-3-18 12:24:00NULLNULLNULL

Note: DatePassedOnTheRequest is really when they passe it onto someone else, or closed it. Until they route it to someone else or close it, the DatePassedOnTheRequest, SolvedIt, and WhatYouDid fields are NULL.

 

I want a query for all closed requests and all open ones (and with their descriptions).

 

Now the way i approached this in my head is to get the last route for each request by ORDERing by DateGotTheRequest DESC for each request. but after that i'm stumped as to if i should use a GROUP BY, or just WHEREs. the GROUP BY i tried basically wont work as group BYs only should pull info in common to all rows right? like requestID and Description stay the same for each request.

 

here is a basic query to start me off:

 

SELECT * FROM routing ro
JOIN request r ON ro.requestID=r.requestID 
ORDER BY requestID ASC, DateGotTheRequest DESC which will give

 

This returns a list of all joined rows:

requestIDDescriptionEmployee_IDDateGotTheRequestDatePassedOnTheRequestSolvedItWhatYouDid

1XP got the Blue Screen of Death.46 2006-3-2 10:58:002006-3-2 11:34:001Had bad drivers, reinstalled XP

1XP got the Blue Screen of Death.97 2006-3-1 14:47:002006-3-2 10:58:000not hardware, passing back to another software guy

1XP got the Blue Screen of Death.12 2006-3-1 12:01:002006-3-1 14:47:000Couldnt solve it, might be hardware issue.

2Keyboard not working. 97 2006-3-5 15:58:002006-3-5 17:17:001keyboard not plugged in

2Keyboard not working. 46 2006-3-1 7:58:002006-3-5 15:58:000not sure, working on this past few days... passing this to Dave

3Needs photoshop installed. 12 2006-3-12 9:23:002006-3-12 10:24:001put order in for photoshop

4Computer not starting. 46 2006-3-18 12:24:00NULLNULLNULL

4Computer not starting. 12 2006-3-17 15:23:002006-3-18 12:24:000checked power supply and motherboard, not sure

 

I want to then pull these following rows out, I would have thought a GROUP BY but seems not to work as it doesnt pull correct info i want (doesnt seem to pull the 'highest row' which for example: for requestID #1 Employee_ID last routed to should be 46 but i think if GROUP BY requestID is added, the row it gets is the one with Employee_ID 12).

requestIDDescriptionEmployee_IDDateGotTheRequestDatePassedOnTheRequestSolvedItWhatYouDid

1XP got the Blue Screen of Death.462006-3-2 10:58:002006-3-2 11:34:00 1Had bad drivers, reinstalled XP

2Keyboard not working.972006-3-5 15:58:002006-3-5 17:17:001keyboard not plugged in

3Needs photoshop installed.122006-3-12 9:23:002006-3-12 10:24:001put order in for photoshop

4Computer not starting.462006-3-18 12:24:00NULLNULLNULL

 

then i want to pull requests WHERE SolvedIt is 1 for my 1st Query(last person it was routed to solved it)

requestIDDescriptionEmployee_IDDateGotTheRequestDatePassedOnTheRequestSolvedItWhatYouDid

1XP got the Blue Screen of Death.462006-3-2 10:58:002006-3-2 11:34:001Had bad drivers, reinstalled XP

2Keyboard not working.972006-3-5 15:58:002006-3-5 17:17:001keyboard not plugged in

3Needs photoshop installed.122006-3-12 9:23:002006-3-12 10:24:001put order in for photoshop

 

And unsolved ones WHERE SolvedIt!=1 (last person it was routed to, hasnt closed it)

requestIDDescriptionEmployee_IDDateGotTheRequestDatePassedOnTheRequestSolvedItWhatYouDid

4Computer not starting.462006-3-18 12:24:00NULLNULLNULL

 

 

any ideas?

[shoz]

Requires MYSQL >= 4.1.

 

SELECT
*
FROM
request AS re
LEFT JOIN
(
    SELECT
    r.*
    FROM
    routing AS r
    INNER JOIN
    (
        SELECT
        requestID, MAX(DateGotTheRequest) AS DateGotTheRequest
        FROM
        routing
        GROUP BY
        requestID
    ) AS rmax
    ON
    r.requestID = rmax.requestID
    AND
    r.DateGotTheRequest = rmax.DateGotTheRequest
) AS ro
ON
re.requestID = ro.requestID 

 

The first thing to note is the LEFT JOIN. If any requests have not been assigned to anyone, then an entry will still show up but all "routing" fields will be NULL. I assume this is a possible case and would be considered an open request.

 

Note also that there are no tests on the SolvedIt field as all returned rows should either be opened are closed if they are in the routing table is my understanding. Is there another state that a request can be in?

 

If you're using an older version of MYSQL post the version number

 

SELECT VERSION();

[arianhojat]

wow awesome, yeh that helps out.

 

Actually I didnt think of a ticket not being assigned to someone as when you enter a ticket, you automatically assign it to someone from the beginning.

All i needed was...

 

    SELECT

    ro2.*

    FROM

    helpdesk.routing AS ro2

    JOIN

 

      (

      SELECT

      requestID

      , MAX(DateTimeEntered) AS DateTimeEntered

      FROM

      helpdesk.routing ro1

      GROUP BY requestID

      ) AS rmax

 

 

    ON

    ro2.requestID = rmax.requestID

    AND

    ro2.DateTimeEntered= rmax.DateTimeEntered

 

    LEFT JOIN helpdesk.request r ON ro2.requestID=r.requestID

 

    WHERE ro2.Closed!=1  // all open tickets

 

i think this is basically the same, ran alot faster for some reason though, maybe cause 1 less subquery i think?

 

 

 

 

[shoz]

wow awesome, yeh that helps out.

 

Actually I didnt think of a ticket not being assigned to someone as when you enter a ticket, you automatically assign it to someone from the beginning.

All i needed was...

 

    SELECT

    ro2.*

    FROM

    helpdesk.routing AS ro2

    JOIN

 

      (

      SELECT

      requestID

      , MAX(DateTimeEntered) AS DateTimeEntered

      FROM

      helpdesk.routing ro1

      GROUP BY requestID

      ) AS rmax

 

 

    ON

    ro2.requestID = rmax.requestID

    AND

    ro2.DateTimeEntered= rmax.DateTimeEntered

 

    LEFT JOIN helpdesk.request r ON ro2.requestID=r.requestID

 

    WHERE ro2.Closed!=1  // all open tickets

 

i think this is basically the same, ran alot faster for some reason though, maybe cause 1 less subquery i think?

 

The queries are not the same. The LEFT JOIN on requests retrieves rows in the routing table even if they don't exist in the requests table. This should not be a logically possible scenario although an error could cause that situation.

 

As for the speed of the query, it should not have been noticeably slow unless it was modified. I'd have to see the EXPLAIN of the queries and the CREATE statements for each. It should be noted that what the LEFT JOIN is doing in my query does to some degree limit how it can be constructed.

 

Note that "WHERE ro2.Closed != 1" doesn't allow MYSQL to use indexes to do the comparison, should make the query that much slower and shouldn't return any "Closed" fields with NULL values. It's my understanding that the latest entry in an open request should have NULL as its Closed entry? Instead you should use "WHERE ro2.Closed IS NULL" I assume the "Closed" field represents the "SolvedIt" field in your query? You didn't mention a Closed field in your first post.

 

EDIT: A short comment on the LEFT JOIN. As you said, the fact that I'm doing 2 subqueries could also make the query I posted slower and I could have done the order of the JOINs similarly to how you've done it to avoid that.

[arianhojat]

all i know is you helped out alot

I needed the description of the request too so hence why i thougt i should go route of joining with request, in fact i guess i should have not done a LEFT JOIN, and done a INNER JOIN since all routes rows will have 1 row in request table.

 

yeh sorry actually i made the tables a bit simpler for the original post:

DateTimeEntered=DateGotTheRequest

Closed=SolvedIt

 

 

...

WHERE ro2.Closed!=1  // all open tickets

now u r right that bascially i am saying here is IS NULL equals open tickets, and IS NOT NULL will be searches for closed tickets. Should I use that IS NULL? or index Closed field and use what i have currently?

 

Thanks alot btw.

[shoz]

WHERE ro2.Closed!=1  // all open tickets

now u r right that bascially i am saying here is IS NULL equals open tickets, and IS NOT NULL will be searches for closed tickets. Should I use that IS NULL? or index Closed field and use what i have currently?

 

You should index the "Closed" field and use "WHERE ro2.Closed IS NULL". An index can't be used for inequality. ie can't be used for "WHERE ro2.Closed != 1". By using Closed != 1 you should also be eliminating all rows where the field "Closed" is NULL. I would have expected the query to give you an empty set because of that.

 

EDIT: Btw, for closed/resolved tickets use "WHERE Closed = 1" not "WHERE Closed IS NOT NULL"

[fenway]

I know I'm just jumping in here, but if closed is simply a flag, don't bother indexing it, MySQL will never use this index.

[shoz]

I know I'm just jumping in here, but if closed is simply a flag, don't bother indexing it, MySQL will never use this index.

 

If MYSQL decided not to use the index because it believes it would be faster not to, then you should be able to force the use of the index. It is a flag but only in a few rows will it be NULL or have a value of 1 so it should be faster to use the index.

 

Actually, now that I've thought about it in the context of the relationship that a row with a "Closed" field with a 1 or a NULL has with the subquery, I don't believe there's any reason to use the subquery. A row with a Closed field of 1 is closed and a row where the field is NULL is open. Those should be the last entries and there should be no need to consider the date.

 

SELECT
*
FROM
requests AS r
INNER JOIN
routing AS ro
ON
r.requestID = ro.requestID
WHERE
ro.Closed IS NULL

 

EDIT: Eventually you'll probably be using an ORDER BY and LIMIT using the date field to go through the entries based on how long each has been open. A multi-column index on (thedatefield, Closed) should be the most appropriate when you decide to do that.

[fenway]

True, if the key distribution is uneven, and there are very few values, it's unlikely to be chosen by the optimizer.