LarsBlog

I was recently asked how to write a query that would contain a combination of inner and outer join (for Oracle).  In this case, the query was to return an entity and all it's attributes.  The entity needed to be returned even if there were no attributes (outer join), but no attributes should be returned that didn't have values (inner join).

I knew this was possible, but the exact syntax was something I had to check up on.  Here it is:

SELECT * FROM mytable
    LEFT JOIN
        (attribute INNER JOIN attributevalue ON attribute.id = attributevalue.attribute_id)
    ON mytable.id = attribute.mytable_id
WHERE mytable.id = 12345