Skip below to avoid a long explanation
So good.
I am working on a corporate intranet to manage client jobs. The work consists of elements: an example is “Creating a six-page website” or “Logo design”.
Each element consists of a set of role-hours, so the “Build the six-page website” can include four hours of “Developer” speed and two hours of “Designer” speed (ok, maybe a little longer :)
Obviously, different customers get different hourly rates. And, although this has already been taken into account in the system, it does not give us enough flexibility. Traditionally, our account managers were more likely ... ad hoc ... with their price: the Build the six-page website element could have included the standard four hours of development for the Bob client, but eight hours for the client, "Harry "
The bear is with me. I will receive the actual code soon.
Elements, of course, are stored in the "Elements" database table, which consists of a little more than an identifier and a text label.
My incomplete solution to the “we need for specific customers” problem is to add the “customer” field to this table. Then we can go through and add any version of the available elements available to the client, customizing them to taste.
When account managers go to add items to their tasks, they should see only the items that are either (a) accessible to everyone, that is, they have a NULL client field, or (b) for the job client.
Bye, so SELECT WHERE.
But this is not going to cut it. If I add the second “Build a Six-Page Website” element specifically for Harry, then the account manager adding the elements to the task for Harry will see both the standard version and the version of this Harry element. This is not good. They should see only the standard version if there is no suitable version for the client.
Ok ... soooo: as well as adding the client field to the element table, add the parent element field. Then we can do something magical self-referential, connected with joining the table to ourselves and get only the corresponding roles.
My long awaited question:
See the 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 take the client identifier parameter and return:
- For customer ID 1, lines 2 and 4
- For customer ID 2, lines 3 and 4
- For customer ID 42, lines 1 and 4
For additional bonus points, the results must include the label of the parent element. 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