CODEWITHSUNDEEP
sqlself-join
Wintermute
Wintermute

Reputation: 3055

SQL self join for parent, child and orphan rows

I'm not sure what the procedure is here - this question is pretty much a direct continuation of this one. However, a new requirement has been introduced that may well necessitate a completely different solution. C'est la vie.

See this new SQLFiddle.

The original solution fetched all Elements for a given Client ID, unless that Element was superseded by a "child" Element, as indicated by the "parent element ID".

That works fine for client-specific Elements that are children of regular Elements.

However, I now have to manage client-specific Elements that aren't children of regular Elements. And the original query isn't fetching them.

I've added a 'NewThing' row to the table - with a client ID and no parent_element ID - but in order to fetch it I'm having to union with another select. I'm aware that this probably isn't the best way of doing it, but cannot figure out how to amend the original query.

So, in the SQLFiddle above, how can I write a single SQL query that will accept a "client ID" parameter (which might be NULL) and return:

Upvotes: 1

Views: 1014

Answers (1)

SalientBrain
SalientBrain

Reputation: 2551

Why not simple like this:

SELECT  mm.*, md.label AS standardized_label FROM mytable md
LEFT JOIN
        mytable mc
ON      mc.parent_element = md.id
        AND mc.client = 1
JOIN    mytable mm
ON      mm.id = COALESCE(mc.id, md.id)
WHERE   md.client IS NULL or (md.client = 1 and md.parent_element IS NULL)

Upvotes: 0

Related Questions