Need help joining two tables with a having count clause

countgroup byjoin;

I am a network guy so I apologize for this…. I have two tables I need to join and pull names out of. pt_basic has the patient names and codes I need, ptc_diagnosis has their diagnosis. I need to pull the names of patients who have one and only one diagnosis. I can join the tables on patient_id. here is what i wrote:

select 
PTC_DIAGNOSIS.PATIENT_ID, 
PT_BASIC.PATIENT_CODE, 
PT_BASIC.NAME_FIRST, 
PT_BASIC.NAME_LAST 
from PTC_DIAGNOSIS 
inner join PT_BASIC on PTC_DIAGNOSIS.PATIENT_ID=PT_BASIC.PATIENT_ID
where PTC_DIAGNOSIS.create_date>'12/01/2015' 
group by PTC_DIAGNOSIS.patient_id 
having COUNT(*)=1

I get the error Column 'PT_BASIC.NAME_FIRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I have moved columns around, tried having pt_basic first and re-written it 15 different ways with similar errors every time. i feel i am close, but maybe not.

thank you

Best Answer

very close only the GROUP BY Columns forgotten

Try this:

select 
PTC_DIAGNOSIS.PATIENT_ID, 
PT_BASIC.PATIENT_CODE, 
PT_BASIC.NAME_FIRST, 
PT_BASIC.NAME_LAST 
from PTC_DIAGNOSIS 
inner join PT_BASIC on PTC_DIAGNOSIS.PATIENT_ID=PT_BASIC.PATIENT_ID
where PTC_DIAGNOSIS.create_date>'12/01/2015' 
group by 
PTC_DIAGNOSIS.PATIENT_ID, 
PT_BASIC.PATIENT_CODE, 
PT_BASIC.NAME_FIRST, 
PT_BASIC.NAME_LAST 
having COUNT(*)=1
Related Question