Sql-server – Querying multiple tables

join;querysql serversubquery

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.

Best Answer

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.

Related Solutions

Sql-server – How to create sums/counts of grouped items over multiple tables

It appears you want COUNT(DISTINCT), which gives you the count of unique values in a column – seems to be exactly what you want.

SELECT s.[SRV_NAME],
       GRP_CNT_INST_NAME = COUNT(DISTINCT i.[INST_NAME]),
       SUM_DB_NAME       = COUNT(*),
       GRP_CNT_USER_NAME = COUNT(DISTINCT u.[USER_NAME]),
       GRP_CNT_APPL_NAME = COUNT(DISTINCT a.[APPL_NAME])
FROM   [DBT].[Server]            AS s
       JOIN [DBT].[Instance]     AS i
            ON  s.ID = i.SRV_ID
       JOIN [DBT].[Database]     AS d
            ON  i.[ID] = d.[INST_ID]
       JOIN [DBT].[Application]  AS a
            ON  d.[APPL_ID] = a.[ID]
       JOIN [DBT].[User]         AS u
            ON  u.ID = d.[USER_ID]
GROUP BY s.[SRV_NAME];

Based on your joins, it appears that DB rows are going to be unique, so you probably do not need COUNT(DISTINCT) in that specific instance. If the output should reflect the number of actual databases, counting distinct names can give you a skewed result, since different instances might have databases with identical names and COUNT(DISTINCT) would see that as a single item.

On the other hand, there would probably be no issue if you counted IDs rather than names:

SELECT s.[SRV_NAME],
       GRP_CNT_INST_NAME = COUNT(DISTINCT i.[ID]),
       SUM_DB_NAME       = COUNT(DISTINCT d.[ID]),
       GRP_CNT_USER_NAME = COUNT(DISTINCT u.[ID]),
       GRP_CNT_APPL_NAME = COUNT(DISTINCT a.[ID])
FROM   [DBT].[Server]            AS s
       JOIN [DBT].[Instance]     AS i
            ON  s.ID = i.SRV_ID
       JOIN [DBT].[Database]     AS d
            ON  i.[ID] = d.[INST_ID]
       JOIN [DBT].[Application]  AS a
            ON  d.[APPL_ID] = a.[ID]
       JOIN [DBT].[User]         AS u
            ON  u.ID = d.[USER_ID]
GROUP BY s.[SRV_NAME];

Combining a parent table with multiple child tables

You need outer joins. Specifically to LEFT JOIN the parent table with each child:

  parent
LEFT JOIN child1 AS c1 ON p.parent_id = c1.child_id
LEFT JOIN child2 AS c2 ON p.parent_id = c2.child_id

and then SELECT from the appropriate tables. Columns that are in both children tables can be combined with COALESCE(). The order of the expressions inside the function matter only if there are cases where an id appears in both childen.

The query will be something like:

SELECT 
    p.parent_id, p.name,
    COALESCE(c1.child_id, c2.child_id) AS child_id,
    COALESCE(c1.date, c2.date) AS date, 
    c1.color, 
    c2.lenght
FROM
    parent AS p
  LEFT JOIN child1 AS c1 ON p.parent_id = c1.child_id
  LEFT JOIN child2 AS c2 ON p.parent_id = c2.child_id ;

Best Answer

Related Solutions

Sql-server – How to create sums/counts of grouped items over multiple tables

Combining a parent table with multiple child tables

Related Question