SQL query to return rows whose column has a common value

Question

How can I query a Microsoft SQL table to return only an InstNum that has all Profile type 'C' or 'U'?

Please see my table structure below.

I tried :

Select InstNum from Table Where Profile = 'C' Group By InstName;

RESULT : 001, 002, 003 EXPECTING : 003 (Because all Profiles in 003 is 'C')

InstNum	Profile
001	U
001	C
001	U
002	U
002	U
002	U
003	C
003	C
003	C

Any help is really appreciated!

Answer 1

Try using an EXCEPT…

SELECT InstNum
FROM Table
WHERE Profile = 'C'

EXCEPT

SELECT InstNum
FROM Table
WHERE Profile <> ‘C'
GROUP BY InstNum;

Answer 2

WITH sameProfiles AS
  (
    SELECT
      COUNT([instNum]) cnt
      , [instNum]
    FROM
      (
        SELECT distinct
          [instNum]
          , [Profile]
        FROM
          myTable1 ) q
    GROUP BY
      [instNum]
    HAVING
      COUNT([instNum]) = 1)
SELECT DISTINCT
  [instnum]
FROM
  myTable1
WHERE
  [profile] = 'C'
and [instNum] in
  (
    SELECT
      [instNum]
    FROM
      sameProfiles);