need help joining two tables with a having count clause 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&gt;'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
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&gt;'12/01/2015' 
group by 
PTC_DIAGNOSIS.PATIENT_ID, 
PT_BASIC.PATIENT_CODE, 
PT_BASIC.NAME_FIRST, 
PT_BASIC.NAME_LAST 
having COUNT(*)=1