sql-server - 如何防止使用此 UNION 选择(或其他方法)重复

Question

我试图通过 UNION SELECT 获得一组特定的结果，但我无法弄清楚如何相应地限制我的结果。本质上，我拥有要返回并以其多个属性的形式显示的对象。这些属性之一是可以对对象进行的注释。有些人对他们有评论，有些人没有。我想显示所有有评论的以及所有没有评论的。我遇到的问题:我的第一个 SELECT 返回所有带有注释的实例，但我的第二个 SELECT 旨在返回没有注释的实例，不仅如此，而且还返回前一个的重复实例，但没有评论。所以最后我得到了所有没有评论的实例，如果有评论，我会得到有评论的实例和一个重复但没有评论的实例。我已经在下面粘贴了我的代码，但是如果您觉得除了使用联合选择之外还有更好的方法来执行此操作，非常感谢您的启发。

一些背景:在我的数据库中，我有一个“交易”主表，一个交易可以有很多评论，我使用带有标记的 DealID 来判断该评论是否处于事件状态。

我的存储过程:

 @Dealership nvarchar(50)

AS

    SELECT DealDate,
    DateReceived,
    Bounced,
    StockNumber,
    LocationName,
    CustomerName,
    Comment 
FROM
    tVehicleDeal,  
    tDealerships, 
    tInternalLocations,
    tVehicleComments,
    tCustomer
WHERE 
(tVehicleDeal.DealershipID = tDealerships.DealershipID) 
AND (tDealerships.DealershipName LIKE '%'+@Dealership+'%')
AND tVehicleDeal.InternalLocationID = tInternalLocations.InternalLocationID
AND tVehicleDeal.DealID = tVehicleComments.DealID
AND tVehicleDeal.CustomerID = tCustomer.CustomerID
AND NOT tInternalLocations.LocationName = 'Down Deal' 
AND tVehicleDeal.Titled IS NULL
AND tVehicleDeal.Archived = 0 

    UNION SELECT DealDate,
    DateReceived,
    Bounced,
    StockNumber,
    LocationName,
    CustomerName,
    NULL
FROM

    tVehicleDeal,  
    tDealerships, 
    tInternalLocations,
    tCustomer

WHERE 
(tVehicleDeal.DealershipID = tDealerships.DealershipID) 
AND (tDealerships.DealershipName LIKE '%'+@Dealership+'%')
AND tVehicleDeal.InternalLocationID = tInternalLocations.InternalLocationID
AND tVehicleDeal.CustomerID = tCustomer.CustomerID
AND NOT tInternalLocations.LocationName = 'Down Deal' 
AND tVehicleDeal.Titled IS NULL
AND tVehicleDeal.Archived = 0 

ORDER BY [DealDate] ASC

-提前致谢!

Answer 1

您需要阅读 SQL-92 Join syntax .您需要对评论表使用外部联接。

试试这个:

SELECT DealDate, DateReceived, Bounced, StockNumber, 
   LocationName, CustomerName, Comment 
FROM tVehicleDeal vd
   Join tDealerships d 
      On d.DealershipID = vd.DealershipID
        And d.DealershipName LIKE '%'+@Dealership+'%'
   Join tInternalLocations il 
      On il.InternalLocationID = vd.InternalLocationID
        And il.LocationName <> 'Down Deal' 
   Join tCustomer cu 
      On cu.CustomerID = vd.CustomerID
   Left Join tVehicleComments c 
      On c.DealID = vd.DealId
WHERE vd.Titled Is Null
   And vd.Archived = 0