(SQL-T-SQL) Select Command to Have only certain values?

Question

Here is my table app_extra:

AppID;AppExtraID
100;0
100;1
100;3
100;7
100;8
100;9
110;0
110;2
110;4
110;7
110;9
115;0
115;2
115;6
115;8
120;0
120;1
120;10
130;0
130;7
130;8
130;10
140;0
140;1
140;3
150;0
150;2
150;6
150;7
150;8
150;10
160;0
160;8
160;10
165;0
165;8
165;10
170;0
170;2
170;8
170;10
180;0
180;1
180;5
180;7
180;10
185;0
185;1
185;7
185;10
190;0
190;2

I would like to know how to have only the AppID that doesn't have 9 and 10 AppExtraID

Thanks!

score 2 · Answer 1 · answered Nov 07 '11 at 20:13

2

select distinct AppId
from app_extra
where AppExtraID not in (9, 10)

Modified to suit your comment's desire.

answered Nov 07 '11 at 20:13

Beat me to it. Great examples. – JClaspill Nov 07 '11 at 20:16
No, this will only removes the rows that contains 9 or 10. I want the T-SQL command to have only the ID's that DOESN'T have 9 or 10. – Martin Gemme Nov 07 '11 at 20:16
@MartinGemme I have modified my answer. This will give you what you need. – Nov 07 '11 at 20:19
@Martin - You asked for a query that returns all rows where the AppExtraID is NOT a 9 or 10. That's what they gave you. Please clarify your request if this is not what you wanted. – Matt M Nov 07 '11 at 20:21

score 2 · Accepted Answer · answered Nov 07 '11 at 20:23

2

I see what you are getting at...

SELECT DISTINCT AppID
FROM app_extra 
WHERE AppID NOT IN 
  (SELECT DISTINCT AppID from app_Extra 
   WHERE AppExtraID IN (9, 10))

The inner select will identify all App ID's which have a 9 or a 10 extra. These rows will then be excluded in the main select. Any AppID which is related to 9 or 10 will be eliminated, even if they also have another extra ID.

answered Nov 07 '11 at 20:23

Tevo D

3,351
21
28

That wil get records that only are missing either 9 or 10 not those that are missing both. – HLGEM Nov 07 '11 at 20:36
That should get all records which have neither a 9 or a 10. – Tevo D Nov 07 '11 at 20:38

score 1 · Answer 3 · answered Nov 07 '11 at 20:15

1

To get a unique list of AppID that fulfill the reqirement:

SELECT AppID 
FROM   tbl
WHERE  AppExtraID NOT IN (9, 10)
GROUP  BY 1

answered Nov 07 '11 at 20:15

Erwin Brandstetter

605,456
145
1,078
1,228

score 0 · Answer 4 · answered Nov 07 '11 at 20:14

0

SELECT DISTINCT AppID FROM app_extra WHERE AppExtraID NOT IN(9,10)

answered Nov 07 '11 at 20:14

UnhandledExcepSean

12,504
2
35
51

No, this will only removes the rows that contains 9 or 10. I want the T-SQL command to have only the ID's that DOESN'T have 9 or 10. – Martin Gemme Nov 07 '11 at 20:16

(SQL-T-SQL) Select Command to Have only certain values?

4 Answers4