• GrpID GrpName PrvID PrvName Spc

    1234 Med Associates (blank) (blank) Mixed

    1234 Med Associates 6875 Bill Ham Cardiology

    1234 Med Associates 3274 Sarah Jones Cardiology

    8975 Pulm Associates (blank) (blank) Mixed

    5781 Babies Inc. (blank) (blank) Pediatrics

    6321 ABC Therapy (blank) (blank) Mixed

    6321 ABC Therapy 4925 Tate Silverman Physical Therapy

    6321 ABC Therapy 3274 Sarah Jones Occupational Therapy

    The column Spc contains either the Specialty of the Group (Grp) if the record has no Provider (Prv), or contains the Specialty of the Provider (Prv).

    I am needing the query to do two things

    1. Return the Groups (Grp) with a Specialty (Spc) equal to Mixed that have no Providers (Prv)

    And

    select GrpId from #mytable where spc like 'mixed' group by GrpId having max(PrvId) =0

    2. Return the Groups (Grp) with a Specialty (Spc) equal to Mixed but where the Providers (Prv) only have one other type of Specialty (Spc).

    select GrpId from

    (select GrpId,varr=(case when PrvID IS null then 0 when PrvID = '' then 0 else 1 end) from #mytable where spc like 'mixed')a group by GrpId

    having sum(varr)=1