Search code examples
sqlsql-serverpivotouter-joinis-empty

Multiple outer joins that can include empty subset

I have a table of objects and an attribute table containing a variety of attributes which an object may or may not have.

I want to get all the attributes along with the object, or null if it doesn't have the attribute.

My first attempt at a query was this:

SELECT o.Id ,
aa.Value AS AttributeA, 
ab.Value AS AttributeB, 
ac.Value AS AttributeC
FROM Objects o
LEFT OUTER JOIN Attributes aa
ON (o.Id = aa.ObjectId)
LEFT OUTER JOIN Attributes ab
ON (o.Id = ab.ObjectId)
LEFT OUTER JOIN Attributes ac    
ON (o.Id = ac.ObjectId)
WHERE (aa.AttributeTypeId = 1 OR aa.AttributeTypeId IS NULL)
AND (ab.AttributeTypeId = 2 OR ab.AttributeTypeId IS NULL)
AND (ac.AttributeTypeId = 3 OR ac.AttributeTypeId IS NULL)
AND o.Id = @objectId

The problem with this is that if no objects yet have, say attribute a, then the aa subset is empty so only objects with no attributes at all are being returned.

How can I get SQL Server to return all objects even with a mixture of attribute scenarios with out getting into a large number of UNIONs (The number of attribute types is 20+)?

Solution

  • When filtering the target of an outer join, you should put the filter on the join.

    ie:

    FROM Objects o 
LEFT OUTER JOIN Attributes aa 
ON (o.Id = aa.ObjectId) and (aa.AttributeTypeId = 1)