CODEWITHSUNDEEP
sql-server
Shane
Shane

Reputation: 542

Select all rows in table separated by commas with condition

A customer enters a ticket and selects a department, or in some cases, multiple departments from the list. My application returns the departments separated by comma's for each Ticket ID. It works great. However, the user may have selected "ALL" departments from the selection (the DEPARTMENT_ID for the selection ALL is '1'). I would then need to return every department in the Departments table separated by commas.

Ticket Table
    TICKET_ID   |  ISSUE
-------------------------
       100      | Power Outage
       101      | Internet is not working

Deartments Table:

 DEPARTMENT_ID | DEPARTMENT
 --------------------------
         1    | ALL
         2    | Accounting
         3    | Human Resources
         4    | Receiving

DepartmentTickets table

DEPARTMENT_TICKETS_ID | TICKET_ID | DEPARTMENT_ID
----------------------------------------------------------
       1              |    100    |     2
       2              |    100    |     3
       3              |    101    |     1

Using my query, ticket 100 shows the following results: Power Outage: Accounting, Human Resources How can I make ticket 101 show the following: Internet is not working: All, Accounting, Human Resources, Receiving

    Select ISSUE, 
    stuff((
    SELECT ', ' + cast(Departments.DEPARTMENT as varchar(max))
    FROM Departments
    left join DepartmentTickets
    ON DepartmentTicket.TICKET_ID = TICKETS.TICKET_ID
    WHERE DepartmentTickets.TICKET_ID = TICKETS.TICKET_ID and DepartmentTickets.DEPARTMENT_ID = Departments.DEPARTMENT_ID       
    FOR XML PATH('')
    ), 1, 2, '') AS DEPARTMENTS
    FROM TICKETS
    WHERE TICKET_ID = '100'
    ORDER BY ISSUE

Upvotes: 0

Views: 77

Answers (1)

sticky bit
sticky bit

Reputation: 37517

First make your inner join in disguise a proper inner join. In the ON clause then add with an OR a check for the department ID being 1.

And by the way, if the IDs are integers not strings, which they appear to be, you shouldn't enclose the literals in single quotes. 

SELECT t.issue, 
       stuff((SELECT ', ' + cast(d.department AS varchar(max))
                     FROM departments d
                          INNER JOIN departmenttickets dt
                                     ON dt.department_id = d.department_id
                                         OR dt.department_id = 1
                     WHERE dt.ticket_id = t.ticket_id
                     FOR XML PATH('')),
             1,
             2,
             '') departments
       FROM tickets t
       WHERE t.ticket_id = 101
       ORDER BY t.issue;

db<>fiddle

Upvotes: 1

Related Questions