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
Please see my table structure below.
I tried :
Select InstNum from Table Where Profile = 'C' Group By InstName;
But I received the results:
I was expecting to receive:
0003 is the only one where all the
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.
Try using an EXCEPT…
SELECT InstNum FROM Table WHERE Profile = 'C' EXCEPT SELECT InstNum FROM Table WHERE Profile <> ‘C' GROUP BY InstNum;
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 🙂