SQL self join for parent, child and orphan rows -

i'm not sure procedure here - question pretty direct continuation of this one. however, new requirement has been introduced may necessitate different solution. c'est la vie.

see new sqlfiddle.

the original solution fetched elements given client id, unless element superseded "child" element, indicated "parent element id".

that works fine client-specific elements children of regular elements.

however, have manage client-specific elements aren't children of regular elements. , original query isn't fetching them.

i've added 'newthing' row table - client id , no parent_element id - in order fetch i'm having union select. i'm aware isn't best way of doing it, cannot figure out how amend original query.

so, in sqlfiddle above, how can write single sql query accept "client id" parameter (which might null) , return:

• for client id 1, rows 2, 4 , 5
• for client id 2, rows 3 , 4
• for client id 42, rows 1 , 4
• for client id null, rows 1 , 4

why not simple this:

select  mm.*, md.label standardized_label mytable md left join         mytable mc on      mc.parent_element = md.id         , mc.client = 1 join    mytable mm on      mm.id = coalesce(mc.id, md.id)   md.client null or (md.client = 1 , md.parent_element null)