时间戳

首页 > 解决方案 > SQL 查询选择支持票证的所有记录及其文件

sql - SQL 查询选择支持票证的所有记录及其文件

问题描述

我在数据库中有 2 个表，其中一个用于支持工单，另一个表用于工单的上传文件，我的问题是如何选择所有工单及其上传的文件，即使是没有工单的工单一份文件。

我的 SQL 查询如下，但它显示有文件的票证，不显示没有文件的票证。

SELECT        
    dbo.tblTickets.id, dbo.tblTickets.uid, 
    dbo.tblTicketReplys.tsenderusername, 
    dbo.tblTicketReplys.tBody, dbo.tblTicketReplys.tdate, 
    dbo.tblTicketReplysAttachments.[fileName], 
    dbo.tblTicketReplysAttachments.fileLink,
    dbo.tblTicketReplysAttachments.fileSize
FROM    
    dbo.tblTickets
INNER JOIN
    dbo.tblTicketReplys ON dbo.tblTickets.id = dbo.tblTicketReplys.tid 
INNER JOIN
    dbo.tblTicketReplysAttachments ON dbo.tblTicketReplys.id = dbo.tblTicketReplysAttachments.repID
WHERE
    (dbo.tblTickets.[uid] = @uid)
    AND (dbo.tblTicketReplys.tid = @tid)
ORDER BY 
    dbo.tblTickets.id ASC

标签： sqlsql-serverdatabase

解决方案

尝试LEFT JOIN

SELECT        
    dbo.tblTickets.id, dbo.tblTickets.uid, dbo.tblTicketReplys.tsenderusername, 
    dbo.tblTicketReplys.tBody, dbo.tblTicketReplys.tdate, dbo.tblTicketReplysAttachments.[fileName], 
    dbo.tblTicketReplysAttachments.fileLink,dbo.tblTicketReplysAttachments.fileSize
FROM    
    dbo.tblTickets
JOIN
    dbo.tblTicketReplys ON dbo.tblTickets.id = dbo.tblTicketReplys.tid 
LEFT JOIN
    dbo.tblTicketReplysAttachments ON dbo.tblTicketReplys.id = dbo.tblTicketReplysAttachments.repID
where (dbo.tblTickets.[uid]=@uid)AND(dbo.tblTicketReplys.tid=@tid)
order by dbo.tblTickets.id asc

推荐阅读