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.