Use (multiple) results from one query as input for another query

Question

I am writing a ticketing program using Vb.net and SQL

I have 2 tables that I need to use in a query. One table (Tickets) holds the all values I need. Second table (Ticketactions) holds all TicketActions linked to a Ticket

What I want is create a search, that searches the following columns:

• Ticket.description
• ticket.ticketID
• Ticketaction.ActionDescrition

I have a search that works well and searches the Tickets Table

        SELECT 
    tickets.TicketNumber, 
    tickets.Description,
    tickets.departmentname,
    tickets.devicename,
    tickets.creatorname,
    tickets.creationdate,
    tickets.ExecutionDate,
    tickets.closedate
    stateName,
    PriorityName,
    FullName,
         FROM tickets
    INNER Join Users On Tickets.AssignedTo = Users.UserID
    INNER Join States On Tickets.StateID = States.StateID 
    INNER Join Priorities On Tickets.priority = Priorities.PriorityID 
         WHERE Description Like '%SEARCHTEXT%' OR TicketNumber Like '%SEARCHTEXT%'

What I want is to expand above query with additional TicketNumbers that are the result of the query in the TicketActions table

    SELECT TicketNumber
    FROM dbo.ticketaction
    WHERE ActionDescription like '%SEARCHTEXT%' 

So the result should be exact as the first query, but with added ticketnumbers that are found by the second query.

Any help would be very welcome, as I have really no idea how to approach this

Answer 1

To answer your literal question, it would be something like...

SELECT 
    tickets.TicketNumber, 
    tickets.Description,
    tickets.departmentname,
    tickets.devicename,
    tickets.creatorname,
    tickets.creationdate,
    tickets.ExecutionDate,
    tickets.closedate
    stateName,
    PriorityName,
    FullName,
FROM
           tickets
INNER Join Users      On Tickets.AssignedTo = Users.UserID
INNER Join States     On Tickets.StateID = States.StateID 
INNER Join Priorities On Tickets.priority = Priorities.PriorityID 
WHERE tickets.Description Like '%SEARCHTEXT%'
   OR tickets.TicketNumber Like '%SEARCHTEXT%'
   OR tickets.TicketNumber IN (SELECT TicketNumber
                                 FROM dbo.ticketaction
                                WHERE ActionDescription like '%SEARCHTEXT%'
                              )

A better solution, could be to use a JOIN on the ticketAction table

SELECT 
    tickets.TicketNumber, 
    tickets.Description,
    tickets.departmentname,
    tickets.devicename,
    tickets.creatorname,
    tickets.creationdate,
    tickets.ExecutionDate,
    tickets.closedate
    stateName,
    PriorityName,
    FullName,
FROM
           tickets
INNER JOIN Users        ON tickets.AssignedTo = Users.UserID
INNER JOIN States       ON tickets.StateID = States.StateID 
INNER JOIN Priorities   ON tickets.priority = Priorities.PriorityID 
INNER JOIN ticketAction ON ticket.TicketNumber = ticketAction.TicketNumber
WHERE tickets.Description Like '%SEARCHTEXT%'
   OR tickets.TicketNumber Like '%SEARCHTEXT%'
   OR ticketAction.ActionDescription LIKE '%SEARCHTEXT%'

(That assumes every ticket has a ticketAction. If that's not the case, use a LEFT JOIN)

Or, EXISTS()...

SELECT 
    tickets.TicketNumber, 
    tickets.Description,
    tickets.departmentname,
    tickets.devicename,
    tickets.creatorname,
    tickets.creationdate,
    tickets.ExecutionDate,
    tickets.closedate
    stateName,
    PriorityName,
    FullName,
FROM
           tickets
INNER Join Users      On Tickets.AssignedTo = Users.UserID
INNER Join States     On Tickets.StateID = States.StateID 
INNER Join Priorities On Tickets.priority = Priorities.PriorityID 
WHERE tickets.Description Like '%SEARCHTEXT%'
   OR tickets.TicketNumber Like '%SEARCHTEXT%'
   OR EXISTS (SELECT *
                FROM dbo.ticketaction
               WHERE ActionDescription like '%SEARCHTEXT%'
                 AND TicketNumber = tickets.TicketNumber
             )

Answer 2

You should be able to do this with a simple union? Below is sample code but may not be what you are exactly looking for.

Union Syntax

SELECT tickets.TicketNumber
    ,tickets.Description
    ,tickets.departmentname
    ,tickets.devicename
    ,tickets.creatorname
    ,tickets.creationdate
    ,tickets.ExecutionDate
    ,tickets.closedate stateName
    ,PriorityName
    ,FullName
FROM tickets
INNER JOIN Users ON Tickets.AssignedTo = Users.UserID
INNER JOIN States ON Tickets.StateID = States.StateID
INNER JOIN Priorities ON Tickets.priority = Priorities.PriorityID
WHERE Description LIKE '%SEARCHTEXT%'
    OR TicketNumber LIKE '%SEARCHTEXT%'

UNION

SELECT t.TicketNumber
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM dbo.ticketaction t
WHERE ActionDescription LIKE '%SEARCHTEXT%'