I am trying to modify a report I found on this site so that it includes the name of the queue where the ticket resides and, ideally, the Category that has been assigned to it.  I think I have the right SELECT statement with:

T.HD_QUEUE_ID AS 'Queue'

But I'm having trouble with the JOIN statement.  I tried this:

JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID AND Q.NAME ='Queue Name')

But it's not returning any results.

Also, if someone could provide the same statements for the Category field, that would be helpful. 

Thank you.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
Don't include the AND Q.NAME = 'Queue Name' in the join statement, just use the ID to match.

The category join is
JOIN HD_CATEGORY C on T.HD_CATEGORY_ID = C.ID

You can then select the category name using C.NAME
Answered 06/02/2016 by: chucksteel
Red Belt

  • Thanks chucksteel, that gets me a bit closer. I removed AND Q.NAME = 'Queue Name' and finally got some results. However, in the Queue column, all that's listed are the Q.IDs (3, 4, 11, etc.). I really don't care about the Q.ID being displayed in the report, but would rather have the actual Q.NAME, such as "IT Helpdesk."

    BTW, I tried AND Q.NAME = 'IT Helpdesk' and it narrowed the results down to Q.ID 3, but that's not really what I want for this report.

    Category statements worked perfectly!
    • I missed that you are selecting T.HD_QUEUE_ID as 'Queue', the selects the queue ID. Change that to Q.NAME as 'Queue' to select the queue's name.
      • Something's still not quite right. Just to simplify things, I created a report that ONLY shows Queue name, but I'm still not getting any results. Here's the full report:


        SELECT Q.NAME AS 'Queue' FROM HD_TICKET T
        JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.NAME)
      • The query should be SELECT Q.NAME AS 'Queue' FROM HD_TICKET T
        JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID)

        You are making a relationship between the two tables based on something that they have in common. In this case the HD_QUEUE_ID column in the HD_TICKET table matches the ID column in the HD_QUEUE table.
      • That got it, thanks!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity

Share