Try this...

IF OBJECT_ID('TempDB..#mytable') IS NOT NULL

DROP TABLE #mytable

CREATE TABLE #mytable

(

ID INT IDENTITY(1,1) NOT NULL

,GrpID INT NULL

,GrpName VARCHAR(50) NULL

,PrvID INT NULL

,PrvName VARCHAR(50) NULL

,Spc VARCHAR(50) NULL

,PRIMARY KEY (ID)

)

INSERT INTO #mytable (GrpID, GrpName, PrvID, PrvName, Spc)

SELECT '1234','Med Associates','','','Mixed' UNION ALL

SELECT '1234','Med Associates','6875','Bill Ham','Cardiology' UNION ALL

SELECT '1234','Med Associates','3274','Sarah Jones','Cardiology' UNION ALL

SELECT '8975','Pulm Associates','','','Mixed' UNION ALL

SELECT '5781','Babies Inc','','','Pediatrics' UNION ALL

SELECT '6321','ABC Therapy','','','Mixed' UNION ALL

SELECT '6321','ABC Therapy','4925','Tate Silverman','Physical Therapy' UNION ALL

SELECT '6321','ABC Therapy','3274','Sarah Jones','Occupational Therapy'

SELECT DISTINCT

m.GrpID

,m.GrpName

,m.Spc

FROM

#mytable AS m

INNER JOIN

(

SELECT

GrpID

,COUNT(DISTINCT Spc) AS SpcCount

FROM

#mytable AS countspc

WHERE

ID > 0

GROUP BY

GrpID

) Sub1

ON m.GrpID = Sub1.GrpID

WHERE

(Spc = 'Mixed' AND PrvID = 0 AND SpcCount = 1)

OR (Spc <> 'Mixed' AND SpcCount = 2)