May 22, 2013 at 11:22 am
I'm trying to join 3 tables. I can do either one of these lines individually but not both together. Any ideas what I'm missing?
The 3 tables are examination, preliminary_exam and pathology.
from
((examination as b inner join examination as pre on b.related_exam_id = pre.id) left join preliminary_exam on pre.procedure_id = preliminary_exam.id)
((examination as b2 inner join examination as pat on b2.related_exam_id = pat.id) left join pathology on pat.procedure_id = pathology.id)
Thanks very much.
May 22, 2013 at 11:28 am
Can't really tell. Are you getting some kind of error?
May 22, 2013 at 11:30 am
Oh, wait:
from
((examination as b inner join examination as pre on b.related_exam_id = pre.id) left join preliminary_exam on pre.procedure_id = preliminary_exam.id)
-- shouldn't there be some kind of join between these?
((examination as b2 inner join examination as pat on b2.related_exam_id = pat.id) left join pathology on pat.procedure_id = pathology.id)
May 22, 2013 at 12:02 pm
I am getting an error, on the second line: incorrect syntax near examination
I'm not sure how to join them but will play around with it. Thanks.
May 22, 2013 at 12:04 pm
Isn't that whole thing pretty much the same as this?
from
examination as b
left join preliminary_exam on b.procedure_id = preliminary_exam.id
left join pathology on b.procedure_id = pathology.id
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 22, 2013 at 2:05 pm
I was trying to capture just cases where the examination fields id and related_exam_id were the same. Maybe I was making it harder than I needed it to be.
Thanks very much for all the help.
May 22, 2013 at 7:11 pm
I think the issue that is muddying the waters is that the "examination" table is apparently a self-referencing hierarchical table. Hopefully there are a fixed number of "steps" from top to bottom of the hierarchy; otherwise, you will need a recursive CTE to traverse the table. I can't really give a complete recursive CTE solution without knowing more about what columns you want and how you intend to use the data; i.e. what the final reporting will look like. But here is a way to relate the tables if there is only one "step" in the hierarchy, assuming that there is a preliminary exam, followed by a regular exam, which can be followed by a second regular exam. In this approach, each "step" in the hierarchy must be treated as a separate instance of the "examination" table, and thus each step will connect to the "pathology" table independently.
from
preliminary_exam as prelim left join
examination as initial_exam on prelim.id = initial_exam.procedure_id left join
pathology as initial_pathology on initial_exam.procedure_id = initial_pathology.id left join
examination as secondary_exam on initial_exam.id = secondary_exam.related_exam_id left join
pathology as secondary_pathology on secondary_exam.procedure_id = secondary_pathology.id
It doesn't seem right that the procedure_id should link the examination table to both the preliminary_exam table and the pathology table, as your example indicates. If that is truly the case in your actual table, it will be difficult to identify the rows that belong to the initial_exam instance of the examination table and which belong to the secondary_exam instance. Depending on the actual table relationships, you may need a WHERE clause that limits the initial_exam instance of the examination table to those examinations that immediately followed the preliminary_exam occurrence.
To reiterate the main idea, this sample code is based on the assumption that the "examination" table is a self-referencing hierarchical table. The key to reflecting this relationship in your results is this join (removing the preliminary_exam and pathology tables to improve clarity):
examination as initial_exam left join
examination as secondary_exam on initial_exam.id = secondary_exam.related_exam_id
Once you grasp the nature of this join of the table to itself in a hierarchical relationship, the rest of the tables you need to include in the FROM clause should fall into place logically.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply