SQL - 连接问题

Question

我有以下查询:

SELECT rt.ID, rt.Name, rt.Rate, rt.Colour, vtb.ID AS 'vtbID', vtb.Value, rt.StdID
FROM Rates AS rt 
LEFT OUTER JOIN VehicleTypeCostsBreakdown AS vtb ON rt.ID = vtb.RateID
LEFT OUTER JOIN VehicleTypeCostsDepots AS vtd ON vtd.ID = vtb.VehicleTypeDepotID AND vtd.DepotID = @DepotID AND vtd.VehicleTypeID = @VehicleTypeID

基本上，我想从费率表中选择所有“费率”，但如果“vtd”表中存在任何对费率的引用，该表具有与@DepotID 和@VehicleTypeID 匹配的参数，我想取回值为了那个原因。如果它没有任何引用，我希望它的“vtb.Value”选择为空。

对于上面的 SQL，它似乎总是为“vtb.Value”值返回一个值，即使参数为空也是如此。我错过了什么吗？

Answer 1

这样试试看。基本上，您将通过 LEFT JOIN 连接到由 VehicleTypeCostsBreakdown 和 VehicleTypeCostsDepots 之间的 INNER JOIN 形成的派生表。只有当您的所有条件都为真时，INNER JOIN 才会匹配。

SELECT rt.ID, rt.Name, rt.Rate, rt.Colour, vtb.ID AS 'vtbID', vtb.Value, rt.StdID
    FROM Rates AS rt 
        LEFT OUTER JOIN VehicleTypeCostsBreakdown AS vtb 
            INNER JOIN VehicleTypeCostsDepots AS vtd 
                ON vtd.ID = vtb.VehicleTypeDepotID 
                    AND vtd.DepotID = @DepotID 
                    AND vtd.VehicleTypeID = @VehicleTypeID
            ON rt.ID = vtb.RateID