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:
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
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
)