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;


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
Go to Top of Page

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 !
Go to Top of Page
   

- Advertisement -