Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working on a query from our ticketing system, and I am having problems figuring out the best way to join these tables together.

I have the following tables/fields:

SR_service: stores ticket data
  SR_Service_RecID
  SR_Status_RecID
  Last_Update
  Date_Req
company: defines a company
  Company_RecID
  Company_Name
SR_type: stores ticket type
  SR_Type_RecID
  Description
SR_Status: stores ticket status
  SR_Status_RecID
  Description
Schedule: the tickets assigned users, and date/time for work*
  RecID (This is the ticketID)
  Member_ID
  Date_Time_Start
  Date_Time_End

*The SCHEDULE table also has a Schedule_Type_RecID, and we are pulling for type '4'. If we join this table with SR_Service, only schedules of type '4' are allowed to be related to tickets in the system.

The first sub-query is for the type of ticket I am looking for. The type of ticket is dependent on what 'board' the ticket belongs to. Leaving out @Board will give me the type of ticket I'm looking for, across all boards.

The second sub-query is making sure I only get tickets that are in NOT closed status. Again, the closed SR_Status_RecID is dependent on the board. I return the closed status of all boards here.

I am at a loss how to get the ticket, company info, schedule info, and ticket status all in one query. The following is the list of what I need: Ticket_ID, status, assigned resources, company name, last update, and required date.

So far, I have come up with:

SELECT s.SR_Service_RecID, s.SR_Status_RecID, c.Company_Name, s.Last_Update, s.Date_Req
FROM SR_Service s INNER JOIN Company c ON s.Company_RecID = c.Company_RecID
WHERE SR_Type_RecID IN (SELECT t.SR_Type_RecID
                        FROM SR_Type t INNER JOIN SR_Board b
                        ON t.SR_Board_RecID = b.SR_Board_RecID
                        WHERE t.description LIKE @Type
                           AND b.Board_Name LIKE @Board)
AND s.SR_Status_RecID IN (SELECT SR_Status_RecID
                          FROM SR_Status
                          WHERE Closed_Flag = 0)

This will get me a good amount of data, but there are a few problems:

  • I only get the status_ID, not the actual description of the status.
  • I am not quite sure how to add the Schedule table into the query to determine when a person is schedule out to perform the ticket tasks.
share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

This will get you the Status Description:


SELECT s.SR_Service_RecID, SR_Status.Description, c.Company_Name, s.Last_Update, s.Date_Req
FROM SR_Service s 
INNER JOIN Company c ON s.Company_RecID = c.Company_RecID
INNER JOIN SR_Status ON s.ST_Status_RecID = SR_Status.SR_Status_RecID
WHERE SR_Type_RecID IN (SELECT t.SR_Type_RecID
                        FROM SR_Type t INNER JOIN SR_Board b
                        ON t.SR_Board_RecID = b.SR_Board_RecID
                        WHERE t.description LIKE @Type
                           AND b.Board_Name LIKE @Board)
AND s.SR_Status_RecID IN (SELECT SR_Status_RecID
                          FROM SR_Status
                          WHERE Closed_Flag = 0)


To add the schedule, you'd need to add a key field from the schedule table to the SR_Service table.

share|improve this answer
    
I updated the table structure to include a field that relates schedule with SR_Service. Would I just INNER JOIN both those tables to get what I'm looking for? –  MJ. Aug 28 '12 at 13:41
    
Inner Join would work. Inner Join ensures the results only contain rows where records match from both tables. In other words there is only a result if there is a SR_Service record and a Schedule record with matching IDs –  Max Vernon Aug 28 '12 at 13:42
    
That did it. Thanks so much! –  MJ. Aug 28 '12 at 13:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.