CODEWITHSUNDEEP
sql
Chris
Chris

Reputation: 7621

SQL - Join issue

I have the following query:

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

Basically, I want to select all 'rates' from Rates table, but if any references to a rate exists in the 'vtd' table, which has parameters that match @DepotID and @VehicleTypeID, I want to bring back the Value for that. If it doesn't have any referenced, I want it the 'vtb.Value' selection to be blank.

With the SQL above, it seems to always return a value for 'vtb.Value' value, even if the parameters are null. Am I missing something?

Upvotes: 3

Views: 81

Answers (3)

mbillard
mbillard

Reputation: 38882

Try this:

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)

You don't need to specify that the LEFT JOIN is an OUTER JOIN and you shouldn't put conditions in the ON section of a JOIN, that's what WHERE is for.

Upvotes: 0

user359040
user359040

Reputation:

Try:

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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135928

Try it this way. Basically, you'll LEFT JOIN to the derived table formed by the INNER JOIN between VehicleTypeCostsBreakdown and VehicleTypeCostsDepots. The INNER JOIN will only match when all of your conditions are true.

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

Upvotes: 2

Related Questions