columns with ONLY some values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

columns with ONLY some values

Author

Topic

jamdakh
Starting Member

2 Posts

Posted - 2014-11-20 : 05:43:11

Hello
I have the following table
account......type
--------------------
A1...........type1
A2...........type6
A3...........type1
A4...........type2
A1...........type1
A2...........type6
.
.
.
A4...........type3
.
.
.
An...........typeX

account and type are not unique

I want to select the account that have ONLY type2 AND/OR type3 type, so my select would return account A4 in my example.

Please help me on the query
thank you
JamD.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2014-11-20 : 07:34:24

Try something like this:


SELECT T.account
  FROM YourTable AS T
 GROUP BY T.account
HAVING COUNT(DISTINCT CASE WHEN T.type = 'type2' OR T.type = 'type3' THEN NULL ELSE 1 END) = 0;


SELECT T.account
  FROM YourTable AS T

EXCEPT

SELECT T.account
  FROM YourTable AS T
 WHERE T.type <> 'type2'
   AND T.type <> 'type3';

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

jamdakh
Starting Member

2 Posts

Posted - 2014-11-20 : 11:42:41

quote:
Originally posted by malpashaa

Try something like this:

SELECT T.account
FROM YourTable AS T
GROUP BY T.account
HAVING COUNT(DISTINCT CASE WHEN T.type = 'type2' OR T.type = 'type3' THEN NULL ELSE 1 END) = 0;

Or

SELECT T.account
FROM YourTable AS T

EXCEPT

SELECT T.account
FROM YourTable AS T
WHERE T.type <> 'type2'
AND T.type <> 'type3';

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

Thank you !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources