CODEWITHSUNDEEP
mysql
Wintermute
Wintermute

Reputation: 3045

SQL self-join to return specific rows

Skip to bottom to avoid long-winded explanation

Ok, so.

I'm working on a company intranet for managing client jobs. Jobs are comprised of Elements: an example element might be "Build a six-page website", or "Design a logo".

Each element consists of a collection of role-hours, so "Build a six-page website" might include four hours of "Developer" rate and two hours of "Designer" rate (ok, maybe a little longer :)

Obviously, different clients get different hourly rates. And, although that's already accounted for in the system, it's not giving us enough flexibilty. Traditionally, our account managers have been rather... ad hoc... with their pricing: the "Build a six-page website" element might include the standard four hours of developer for client "Bob", but eight hours for client "Harry".

Bear with me. I will get to actual code soon.

Elements are, of course, stored in the "Elements" database table - which is composed of little more than an ID and a text label.

My work-in-progress solution to the "we need client-specific elements" problem is to add a "client" field to this table. We can then go through and add any client-specific versions of the available elements, tweaking them to taste.

When the account managers go to add elements to their jobs, they should only see elements that are either (a) available to anyone - that is, they have a NULL client field, or (b) specific to the job client.

So far, so SELECT WHERE.

But that isn't going to cut it. If I add a second "Build a six-page website" element specifically for Harry, then an account manager adding elements to a job for Harry will see both the standard version, and Harry's version of the element. This is no good. They should only see the standard version if there's not an applicable client-specific version.

Ok... soooo: as well as adding a "client" field to the elements table, add a "parent element" field. We can then do something magically self-referential involving joining the table to itself, and fetch only the relevant roles.

My long-awaited question is thus:

Oh look, an actual question

id  label           client  parent_element
1   Standard Thing  NULL    NULL
2   Harrys Thing    1       1
3   Bobs Thing      2       1
4   Different Thing NULL    NULL

Given this table structure, how can I write a single SQL query that will accept a "client ID" parameter and return:

For extra bonus points, the results should include the parent element label. So for client ID 1, for example:

id  label           standardised_label      client  parent_element
2   Harrys Thing    Standard Thing          1       1
4   Different Thing Different Thing         NULL    NULL

Upvotes: 4

Views: 228

Answers (1)

Quassnoi
Quassnoi

Reputation: 425471

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

Create an index on (client, parent_element) for this to work fast.

See SQLFiddle.

Upvotes: 3

Related Questions