sql - SQL-连接问题

我有以下查询:

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，即使参数为null，它似乎始终会返回“vtb.Value”值的值。我想念什么吗？

最佳答案

尝试这种方式。基本上，您将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