Grouping inner and outer joins

Filed under: TechNotes, Oracle, SQL — lars @ 07:32:55 pm

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

Comments

No Comments for this post yet...

    Leave a comment

    Allowed XHTML tags: <p, ul, ol, li, dl, dt, dd, address, blockquote, ins, del, span, bdo, br, em, strong, dfn, code, samp, kdb, var, cite, abbr, acronym, q, sub, sup, tt, i, b, big, small>


    Options:
    (Line breaks become <br />)
    (Set cookies for name, email & url)




    powered by  b2evolution