SQL subqueries and joins

A while back, a script I had written to dig stats out of our PACS database stopped working for some reason. Since I was mostly done with that project, I didn’t mess around with it too much.

Now I’ve been asked to resurrect it to gather more usage data in advance of a planned migration.

The PACS database uses Oracle, but all my DB experience is with MySQL. This is the main query I’m dealing with (it runs from a shell script, which is why the variables are there)

select
count(study_uid),
avg(num_images),
sum(num_images),
max(num_images),
min(num_images)
from dosr_study
where study_ref in (
select
study_ref
from dosr_series
where modality='${modality}'
)
and study_date between
'${c_year}${c_month}01' and '${c_year}${c_month}31'

For some reason this query stopped running. Well, it runs, but it just doesn’t spit out any numbers like it used to.

If I reformulate the query into what I think is the equivalent query without the subquery,

select distinct
count(study_uid),
avg(num_images),
sum(num_images),
max(num_images),
min(num_images)
from dosr_study, dosr_series
where dosr_study.study_ref = dosr_series.study_ref
and dosr_series.modality='${modality}'
and study_date between
'${c_year}${c_month}01' and '${c_year}${c_month}31'

the query works, but returns different results from what I got before when the original query worked.

I suppose I’ll need to find out more about Oracle SQL so I can figure out why the original query stopped working in the first place.

Update: One of my friends thinks I probably haven’t rewritten the query correctly (what I was thinking as well) and suggested changing

from dosr_study, dosr_series
where dosr_study.study_ref = dosr_series.study_ref

to

from dosr_study
join dosr_series on dosr_study.study_ref=dosr_series.study_ref