SQL - Join issue

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

使用上面的SQL，它似乎总是返回'vtb '的值。Value' Value，即使参数为空。我错过什么了吗?

Answer 1

试试这样做。基本上，您将LEFT JOIN到由VehicleTypeCostsBreakdown和vehicletypecostsdepot之间的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

Answer 2

尝试:

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

Answer 3

试试这个:

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

您不需要指定LEFT JOIN是OUTER JOIN，也不应该在JOIN的ON部分添加条件，这就是WHERE的作用。