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.
| 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 |
 |
|
|
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 ! |
|
|
|
|
|
|
columns with ONLY some values