使用一个查询中的(多个)结果作为另一个查询的输入

Question

我正在使用Vb.net和SQL 编写一个票务程序

我有两个表需要在查询中使用。一张表(Tickets(包含了我需要的所有值。第二个表(Ticketactions(包含链接到Ticket 的所有TicketAction

我想要的是创建一个搜索，搜索以下列：

• Ticket.description
• ticket.tticketID
• Ticketaction.ActionDescrition

我有一个搜索效果很好，可以搜索票证表

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%'

我想要的是用TicketActions表中查询的结果来扩展上面的查询

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

因此，结果应该与第一个查询完全相同，但添加了第二个查询找到的股票代码。

任何帮助都是非常受欢迎的，因为我真的不知道如何处理这个

Answer 1

要回答你的字面问题，它应该是。。。

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%'
)

一个更好的解决方案可以是在ticketAction表上使用JOIN

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%'

(假设每个票证都有ticketAction。如果不是这样，请使用LEFT JOIN(

或者，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

您应该能够用一个简单的联合来实现这一点吗？下面是示例代码，但可能不是您想要的。

联合语法

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%'