vilyana (9/19/2016)
Thank you for the concern.The ERD and the View columns in the image are exactly what they need to be to illustrate the starting point and desired result (and this is all they are intended to be). Every other piece of info is removed to simplify as much as possible and focus on the actual question which @frederico_fonseca provided a solution for (clearly faster and better than mine - I used the pattern to write the full solution on the real tables) . And yes, 0 to N Treatments, 0 to N Therapies for each treatment, 0 to N Med Courses for each treatment are possible (as shown in the erd), but only the first 2 of each based on ID (not date) are required in this particular view.
The only problem with the ERD is that the column and table names are not obscured with a generic naming convention so there won't be anyone picturing over-medicated (or worse) patients without knowing anything about the context, purpose or the actual system. I wonder if I can still finish the last semester of grad school and work full time from jail? Ugh, it would be really unfortunate getting locked up for limiting the info on a sample diagram and column names in a post 🙂 I promise to use Tbl1, Col1,.., ColN next time I ask for help with improving my SQL code.
I've marked the working solution to my question. Now I’ll be going back to assisting people who need care.
Kind regards,
Vilyana
It's not any of that that we're concerned with, Vilyana. It's the fact that the data between the therapy and the meds is going to be arbitrarily joined by date or the order of an Identity column instead of DRI (non-null Foreign Keys, in this case). Such an arbitrary join doesn't guarantee that the correct meds will go with the correct therapy and could even allow for the duplicate administration of the meds or for meds to be given in the wrong order or even not at all. That could kill someone and it needs to be fixed not ASAP but right now.
--Jeff Moden
Change is inevitable... Change for the better is not.