The example here looks like 2 out of a set of at least 3 tables.
I expect that the database has
PatientDetails , holding PatientID and name, perhaps address, date of birth… who knows
Patient (badly named, should be PatientStudies or similar ) which connects Patients with Studies … one patient can have zero to many studies. It holds the PatientID and the StudyID
Study which describes the Study
To see all the data, you might link together all 3
PatientsDetails.PatientID = Patient.PatientID and Patient.StudyID = StudyID
If you used an INNER join here, you would get any patient that had at least one study.
You would see several rows for a patient that had several studies
And no rows for patients that had no studies.
To see all patients, even if they have no studies, you need an OUTER join, which means ‘it doesnt matter if there are no studies’
To rework your query in that way:
select
pt.PatientID, pt.StudyID, st.StudyDesciption
from Patient pt left outer join Study st
on pt.StudyID = st.StudyID
where st.PatientID = ‘foo’
remove the left outer if you only want rows for patients who do have studies.
PatientID here is surrounded by single quotes, but it may be a number, in which case you dont need the quotes
Thanks all.
I never quite wrapped my head around joins. What dave S did seems more like something I can understand.
I’m not sure I understand why join was necessary when what Dave wrote seems to me to be how it should be.
Join is the more modern syntax for the same thing dave posted which I find easier to read by a mile most times but then I started with sql in 1989 or so which predates this syntax by many years
One of the things that JOIN syntax avoids is all the vendor specific extensions to sql that they used to write inner & outer joins "old style) Like = or = which meant “left join” or “right join” in some systems and was easy to overlook
One things Dave’s query wont do that the suggested JOIN will do is return ALL patients whether they have a study associated or not (thats where the outer parts comes in to play)
What LEFT OUTER JOIN means is “keep EVERY ROW from the LEFT HAND” tables regardless of whether they have a match on the right
SO in the case of
select
pt.PatientID, pt.StudyID, st.StudyDesciption
from Patient pt
left outer join Study st
on pt.StudyID = st.StudyID
where st.PatientID = ‘foo’
EVERY row from pt (patient) would be listed regardless of whether a match in study (st) was found using pt.StudyID = st.StudyID
Daves will only list it IF there IS a match - so slightly different
If I might continue this thread…
I have two tables, a study table and a series table.
A study has many series.
My study table was suppose to have a column called modalities but doesn’t.
Modalities when examined (in other databases) looks like CT/MR/CR/OT
My series table has a column called modality
Each series can have a modality.
I want to create a study table query that ‘fabricates’ modalities by querying the series table for a unique list of modalities and building the modalities column.
It’s like somehow I have to build a select statement for the study table that contains a pseudo column built fom a select statement of the series table for the study, but it has to be 1 sql statement.
Glad I ran into this post as I’m starting a new project for work and trying to plan things out and what DaveS posted also makes more sense to me then joins or I should say I can understand what Dave posted better then joins.
Just be aware of the slight difference in the two queries
Say you have a list of employees & spouses
And you want on big list so you can remember everyones name & their spouses name
Not everyone is married so some will have a spouse entry and some wont (say its in another table just for fun
So what we have is a table of employees and a tables of spouses
Employees
employee_id
name
Spouses
spouses_employee_id
name
nice & simple
If we have data like
Employee
1 Dave S
2 Norman Palardy
Spouses
1 Maria S (made this one up so ... forgive me)
you can see that I am not married (dont tell my wife)
So how with a single query do you get one list that includes all employees & spouses regardless of married status ?
Daves query wont do it
It would only list those who HAVE spouses
So in this case when you need “a list of X whether they have a related Y or not” outer joins are likely required
LEFT and RIGHT literally refer to the two tables in any join (the first one is “left” and the second “right”
inner means return the result if there is a match in the specified columns
outer returns the (left or right) either way (match or no)
I think I have all this right but its mostly off the top of my head so … old age memory & all that
And there are more options (cross which is fun)
This post is decent walking through a pile of options with diagrams & decent explanations & examples
ok … so to get a “distinct list” of modalities thats literally using the key word “distinct”
the trickiest part is taking a pile of rows and turning it into one big long string (something sql isn’t great at)
lets assume a “study” has a “study id” (or some unique id) and that the series also have that study id on them (otherwise we’re kind of stuck)
getting the distinct list of series as a pile of rows is something like
select DISTINCT series.modality from series, study where series.study_id = study.study_id AND STUDY.STUDY_ID = ....
which lets you get the list (as separate rows) of modalities for a specific study (where the … is you put the study_id)
like I said the hardest part is getting all those rows as one big long string as if they were the single value for a column
SOME db servers actually have extended SQL to do this transform specifically because its quite useful
not knowing what DB you’re using its hard to say what you should or can do
sqlite has group_concat which should work with the right query
Yes… if you want all the items in Table A, regardless of if there is a match in Table B then you DO need to use a LEFT join
but I think what I posted met the conditions of the original requirement.
otherwise it would be
select
pt.PatientID,
pt.StudyID
st.StudyDesciption
from Patient pt
LEFT JOIN Study st
ON pt.StudyID=st.StudyID
where st.PatientID = "foo"
or better yet
select
pt.PatientID,
pt.StudyID
NVL(st.StudyDesciption,(NO DESCRIPTION') as StudyDescription
from Patient pt
LEFT JOIN Study st
ON pt.StudyID=st.StudyID
where st.PatientID = "foo"
for PACS design, table structure should follow hierarchy in which lower level references the parent.
For example:
T_PATIENT
T_STUDY - should store foreign key for patient_pk
T_SERIES - should store foreign key for study_pk
T_INSTANCE - should store foreign key for series_pk
for oracle, you should be able to use listagg function to aggregate the series modality list for study level.
for example (pseudo code):
select a.patient_mrn, b.accession_number, b.study_instance_uid, listagg(c.modality, ' / ' ) within group (order by c.modality) as modalities_in_study
from T_PATIENT a
join T_STUDY b on b.patient_id = a.patient_id
join T_SERIES c on c.study_id = b.study_id
where a.patient_mrn = 'foo';
[code]select a.patient_mrn, b.accession_number, b.study_instance_uid, listagg(c.modality, ’ / ’ ) within group (order by c.modality) as modalities_in_study
from T_PATIENT a
join T_STUDY b on b.patient_id = a.patient_id
join T_SERIES c on c.study_id = b.study_id
where a.patient_mrn = ‘foo’
group by a.patient_mrn, b.accession_number, b.study_instance_uid;[/code]
