SQL query to return rows whose column has a common value



  • How can I query a Microsoft SQL table to return only the InstNum that has all Profile types C or U?

    Please see my table structure below.

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

    I tried :

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

    But I received the results:

    InstNum
    001
    002
    003

    I was expecting to receive:

    InstNum
    003

    Because the InstNum of 0003 is the only one where all the Profiles are C.

    What am I doing wrong? Does anybody have an idea how I can achieve this?



  • Try using an EXCEPT…

    SELECT InstNum
    FROM Table
    WHERE Profile = 'C'
    

    EXCEPT

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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2