SQL query to return rows whose column has a common value

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Try using an EXCEPT…

SELECT InstNum
FROM Table
WHERE Profile = 'C'

EXCEPT

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

Method 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);

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply