SQL - Join issue

Question

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?

When you say that you always get a return value for vtb.Value even if the parameters are null, is vtb.Value returning as NULL or are you getting an actual value? — Joel Beckham, Dec 08 '11 at 16:41

score 2 · Accepted Answer · answered Dec 08 '11 at 16:42

2

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

answered Dec 08 '11 at 16:42

Joe Stefanelli

132,803
19
237
235

An inner join to a table on the outer side of an outer join turns it back into an inner join, much like a WHERE clause would. – Dec 08 '11 at 16:53
@MarkBannister - Look at the position of the `ON` clauses. The `LEFT JOIN` logically happens last. This can also be written as a `RIGHT JOIN` more straight forwardly. [See my answer here for specific examples](http://stackoverflow.com/a/7313507/73226) – Martin Smith Dec 08 '11 at 17:29
How neat, I hadn't realised you could do that! (I'd remove my earlier comment, but then Martin's response would look a bit odd.) – Dec 08 '11 at 17:48

mbillard · Answer 2 · 2011-12-08T17:04:17.810

0

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.

edited Dec 08 '11 at 17:04

answered Dec 08 '11 at 16:41

mbillard

38,386
18
74
98

4

If you reference columns from a LEFT JOINed table in the WHERE clause, you force that join to behave as if it were an INNER JOIN. – Joe Stefanelli Dec 08 '11 at 16:43
@JoeStefanelli: Indeed, changed my answer. Thanks for pointing this out. – mbillard Dec 08 '11 at 17:05

score 0 · Answer 3 · answered Dec 08 '11 at 16:48

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

SQL - Join issue

3 Answers3