K1000 Custom Ticket Rules - Using an Inline View to Query & Update Tables in One Pass
______________________________________________________________________________

A question recently came up regarding synchronizing a field between tickets that reference other (earlier) tickets, and one of my fellow ninjas advised that this would require reading a value from the first ticket and then applying that value to the referring ticket(s), something that isn't possible with MySQL.  While this is definitely true for a "simple" query, inline views (uncorrelated subqueries) actually make this possible through the use of a temporary table that the main ("simple") query can then reference.

In this article, I'll explain how to determine which tables to use, how to construct "interim" queries and (finally) how to put everything together in a custom ticket rule.
________________________

Original question:
http://www.itninja.com/question/ticket-rule-to-synchronize-status-of-referrers-tickets-with-current-ticket
________________________

For a full analysis and explanation of inline views and other advanced MySQL techniques:

K1000 Reports - Advanced MySQL Query Techniques - Parsing Multiple Substrings from a Field Using Temporary Tables
http://www.itninja.com/blog/view/k1000-reports-advanced-mysql-query-techniques-parsing-multiple-substrings-from-a-field-using-temporary-tables
________________________

For more basic SQL query construction tips and MySQL Query Browser setup:

Primer for Writing SQL Select Statement Queries on the K1000 (w/Custom SQL Report example)
http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example
______________________________________________________________________________

1) Determining which tables to use

Since the original question dealt with Service Desk tickets and their statuses, the first two tables we'll need to consider are HD_TICKET (where the ticket ID, title, ticket statuses and other info are kept) and HD_STATUS (where the ticket statuses are actually defined).  The other important point concerned referencing tickets (the See Also: field in the GUI), and this information is kept in the HD_TICKET_RELATED table (which lists all tickets having something listed or being referenced in the See Also: field).

Getting these all together in a single query requires the use of JOIN statements on common data.  Since we'll be querying and updating the tickets themselves, it makes sense to use HD_TICKET as the main table in the query (i.e. FROM HD_TICKET).  This leaves us with joining the other tables.  HD_STATUS is pretty straightforward, as HD_STATUS.ID directly corresponds to HD_TICKET.HD_STATUS_ID, so we can use JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID) to enable pulling data from this table.
________________________

However, with HD_TICKET_RELATED, we have two possible columns to join on - FROM_ID and TO_ID.  So, which one should be used?  A simple query and review of the tickets themselves will help us determine this:

*Example Query*
SELECT * FROM HD_TICKET_RELATED

*Example Output*
FROM_ID   TO_ID
21903     19968
etc...
________________________

It would make sense that the higher order ticket ID (21903) would be the referencing ticket, and reviewing the ticket in the Service Desk confirms this - the See Also: field has the 19968 ticket listed.  Therefore, since we want to update the referencing ticket, we'll note this as such:

R.FROM_ID = "referring ticket" has the See Also ticket listed
R.TO_ID = "referenced ticket" the See Also ticket that is referenced

For example:

FROM (Referring)           > 21903
TO (See Also - Referenced) > 19968
______________________________________________________________________________

2) Constructing interim queries

First, we'll build our main query using the FROM_ID column for the join, so that we pull (SELECT) all of the referring tickets.  I've also included the ticket ID, ticket title (for easy reference), status name and status ID for clarity:

SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
HD_TICKET.HD_STATUS_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
ORDER BY HD_TICKET.ID
________________________

The next step will be to pull the same data from the referenced ticket - in this case we're mainly concerned with HD_STATUS_ID, although it could be any column.  This can be done with the following query:

SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
HD_TICKET.HD_STATUS_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID)
ORDER BY HD_TICKET.ID
________________________

Note that this is *exactly* the same as the previous query, the only difference being that HD_TICKET_RELATED has been joined using the TO_ID column to pull (SELECT) all of the referenced tickets.
______________________________________________________________________________

3) Constructing the ticket rule's SELECT query

OK, so now we have two queries - one for the referring tickets and the next for referenced tickets.  Now what?  Well, remember that the goal is to pull the status from the referenced tickets and then apply it to the referring tickets.  So we'll put the referenced tickets in an inline view (SOURCE) so that the main (referring) query can access this data (and later apply it using an update query).  The original SELECT query is as follows, but as you'll see there's actually a better way to construct it:

SELECT DISTINCT SOURCE.ID as SID, SOURCE.TITLE as STITLE,
SOURCE.NAME as SNAME, SOURCE.HD_STATUS_ID,
HD_TICKET.ID, HD_TICKET.TITLE, S.NAME, HD_TICKET.HD_STATUS_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
  HD_TICKET.HD_STATUS_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.ID = R.TO_ID)
WHERE HD_TICKET.HD_STATUS_ID != SOURCE.HD_STATUS_ID
ORDER BY HD_TICKET.ID

This results in the same fields being listed for both the referenced tickets (SOURCE) and the referring table, in a nice arrangement that allows for quick comparison of the ID, title and status columns.  Note that it is ultimately the HD_TICKET.HD_STATUS_ID column that we want to change (UPDATE) in the referring tickets, as this is where a ticket's status is kept - all further details (such as the actual status name) are referenced in the STATUS table.  The WHERE statement simply says "ignore tickets with matching status IDs", which will focus the UPDATE query to only run against tickets that need to be updated (i.e. those whose status IDs don't match).
________________________

One of the main considerations with inline views is determining which columns to use for the JOIN to the main query.  As with any JOIN, we want to use columns that share common elements without being too generic.  Using the above query as an example, STATUS.NAME meets the first criteria (same data in the main query and inline view), but fails on the second (not granular/specific enough).  HD_TICKET.TITLE meets the second criteria (very specific), but typically will fail on the first (unless there is a guarantee that the titles of referencing tickets are *exactly* the same as the referenced tickets - personally, I wouldn't bet on this as it's an open text field).

The only columns that actually meet both criteria are HD_TICKET.ID, HD_TICKET_RELATED.FROM_ID and HD_TICKET_RELATED.TO_ID - which makes sense as these are the primary keys for their respective tables.  So the question now is - which to use?  Trial and error will get the job done (as I did with the original query above!), however it's always good to step back and review things again later to make sure the construction makes sense (note to self... ^_^).  Keep in mind that we want to target the referring tickets, so joining to the main query's HD_TICKET.ID column seems natural enough.  

From there, it's basically process of elimination.  We know that the referenced tickets have different IDs than the referring tickets, so we can't use SOURCE.ID (the inline view's HD_TICKET.ID) for the join.  The same reasoning applies to using SOURCE.TO_ID, the inline view's HD_TICKET_RELATED.TO_ID column (not there, but we *could* add it).  Recall that TO_ID specifies the referenced tickets, while the main query's HD_TICKET.ID column specifies the referring tickets, and you'll see that it's no better than trying to use SOURCE.ID.  So that leaves us with SOURCE.FROM_ID, which specifies the referring tickets and lines up exactly with the results of HD_TICKET.ID in the main query (meeting both criteria, as discussed a moment ago).  

Now the issue is - we don't have FROM_ID in the inline view.  Not a problem, just add it in - and keep in mind that columns listed in the inline view do not need to be called in the main query (sometimes they are just "utility" columns for JOINs, etc).  This results in a more efficient query than the one above, as the JOIN between the main query and the inline view are now a 1-to-1 match and no DISTINCT cleanup is required:

SELECT SOURCE.ID as SID, SOURCE.TITLE as STITLE,
SOURCE.NAME as SNAME, SOURCE.HD_STATUS_ID,
HD_TICKET.ID, HD_TICKET.TITLE, S.NAME, HD_TICKET.HD_STATUS_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
  HD_TICKET.HD_STATUS_ID, R.FROM_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.FROM_ID = HD_TICKET.ID)
WHERE HD_TICKET.HD_STATUS_ID != SOURCE.HD_STATUS_ID
ORDER BY HD_TICKET.ID

The "problem" with the original query (and often the need for DISTINCT will highlight this fact) is that the JOIN on the main query and inline view ended up with some extra results whenever there were multiple referring tickets for one referenced ticket.  I wasn't initially aware of this circumstance and (trying to get it working quickly) "fixed" it with a DISTINCT statement, but the more efficient/elegant solution is to prevent the extraneous results in the first place (as is done in the second SELECT query).  This is where constructing queries becomes more of an art than a science, as both return the same results.  But adding extra "informational" columns (such as TITLE) can be a great help in making sense of what the query returns.
________________________

I should note that the comment was made in the question that this was a complicated SELECT query, and while it is true that it is more complex than absolutely necessary for the SELECT query, building it out like this was in fact necessary to derive the UPDATE query.  For anyone interested, the most basic functional SELECT query would only consist of the HD_TICKET table and the JOIN to the HD_TICKET_RELATED table on FROM_ID, so that the referring tickets will be listed:

SELECT HD_TICKET.ID
FROM HD_TICKET
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)

That being said, if there are a number of referring tickets, I would still use the "complicated" query so that only tickets without matching HD_STATUS_ID values will be passed to the UPDATE query.  The "complicated" query is also perfect for running as a SQL report, should the need arise.
______________________________________________________________________________

4) Constructing the ticket rule's UPDATE query

With the SELECT query constructed, it is just a matter of changing it slightly to create the UPDATE query.  In this case, specify the HD_TICKET table for the UPDATE statement, specify the HD_STATUS_ID columns in the SET statement and (finally) include the WHERE HD_TICKET.ID in (<TICKET_IDS>) statement so it will only apply to the ticket that is being saved (as the rule is set to run on ticket save).  Aside from HD_TICKET.ID and HD_TICKET.HD_STATUS_ID, the other columns in the inline view are most likely unnecessary (for what it's worth), so feel free to experiment and whittle it down further as you see fit.

UPDATE HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE,
  HD_TICKET.HD_STATUS_ID, S.NAME
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.ID = R.TO_ID)
SET HD_TICKET.HD_STATUS_ID = SOURCE.HD_STATUS_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)
________________________

Another version of the UPDATE query that should work (based on the revised SELECT query above):

UPDATE HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.HD_STATUS_ID, R.FROM_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.FROM_ID = HD_TICKET.ID)
SET HD_TICKET.HD_STATUS_ID = SOURCE.HD_STATUS_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)
________________________

As the original author of the question found, updating (synchronizing) additional fields just requires adding the column(s) to the inline view and to the SET statement.  Here is a variation that also updates the ticket's owner (HD_TICKET.OWNER_ID):

UPDATE HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE,
  HD_TICKET.HD_STATUS_ID, S.NAME, HD_TICKET.OWNER_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.ID = R.TO_ID)
SET HD_TICKET.HD_STATUS_ID = SOURCE.HD_STATUS_ID,
HD_TICKET.OWNER_ID = SOURCE.OWNER_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)
______________________________________________________________________________

5) Beware of aliasing the updated table in ticket rules

One "gotcha" I ran across (my "Arg..." comment in the original question) was related to the SELECT query not working properly on ticket save with the HD_TICKET table aliased.  My original construction was like this:

SELECT DISTINCT SOURCE.ID as SID, SOURCE.TITLE as STITLE,
SOURCE.NAME as SNAME, SOURCE.HD_STATUS_ID,
T.ID, T.TITLE, S.NAME, T.HD_STATUS_ID
FROM HD_TICKET T
JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = T.ID)
JOIN
 (SELECT T.ID, T.TITLE, S.NAME, T.HD_STATUS_ID
  FROM HD_TICKET T
  JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = T.ID))
SOURCE on (SOURCE.ID = R.TO_ID)
WHERE T.HD_STATUS_ID != SOURCE.HD_STATUS_ID
ORDER BY T.ID

UPDATE HD_TICKET T
JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = T.ID)
JOIN
 (SELECT T.ID, T.TITLE,
  T.HD_STATUS_ID, S.NAME
  FROM HD_TICKET T
  JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = T.ID))
SOURCE on (SOURCE.ID = R.TO_ID)
SET T.HD_STATUS_ID = SOURCE.HD_STATUS_ID
WHERE T.ID in (<TICKET_IDS>)
________________________

It worked fine in the MySQL Query Browser *and* when I ran the ticket rule manually.  However... when the ticket was saved (and the ticket rule ran on ticket save), the following error popped up in the ticket rule's Run Log field:

08:24> Starting: Fri, 05 Oct 2012 15:08:24 -0400
08:24> Executing Select Query...
08:24> mysql error: [1054: Unknown column 'HD_TICKET.ID' in 'on clause'] in EXECUTE("SELECT DISTINCT SOURCE.ID as SID, SOURCE.TITLE as STITLE,
SOURCE.NAME as SNAME,SOURCE.HD_STATUS_ID,
T.ID, T.TITLE, S.NAME, T.HD_STATUS_ID
FROM HD_TICKET T
JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = T.ID)
JOIN
 (SELECT T.ID, T.TITLE, S.NAME, T.HD_STATUS_ID
  FROM HD_TICKET T
  JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = T.ID))
SOURCE on (SOURCE.ID = R.TO_ID) and (HD_TICKET.ID = 21903) ")

Fortunately, I recalled running into this before (many moons ago...) and removing the aliases from the SELECT query took care of things.  I did the same for the UPDATE query for consistency, although this apparently worked with the aliases in place (as was noted prior to this change, and as I've seen done in other UPDATE queries).
______________________________________________________________________________

6) Custom Ticket Rule Construction

*Title*
Synch Ticket Status With Reference Ticket Status

*Order*
50

*Frequency*
on Ticket Save

*Enabled*
<checked>

*Select Query*
SELECT SOURCE.ID as SID, SOURCE.TITLE as STITLE,
SOURCE.NAME as SNAME, SOURCE.HD_STATUS_ID,
HD_TICKET.ID, HD_TICKET.TITLE, S.NAME, HD_TICKET.HD_STATUS_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
  HD_TICKET.HD_STATUS_ID, R.FROM_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.FROM_ID = HD_TICKET.ID)
WHERE HD_TICKET.HD_STATUS_ID != SOURCE.HD_STATUS_ID
ORDER BY HD_TICKET.ID

*Update Query*
UPDATE HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.HD_STATUS_ID, R.FROM_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.FROM_ID = HD_TICKET.ID)
SET HD_TICKET.HD_STATUS_ID = SOURCE.HD_STATUS_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)
______________________________________________________________________________

Hope that helps!

John