SELECT WHERE NUMBER IS NEVER 1 OR 2

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

General SQL Server Forums

New to SQL Server Programming

SELECT WHERE NUMBER IS NEVER 1 OR 2

Author

Topic

P.Mahendarajah
Starting Member

3 Posts

Posted - 2013-10-18 : 04:09:35

I have a problem where I essentially want to find all the B_ID numbers that do not have any ones or twos next to them in the whole table (see table below).

I come up with this query, however it is wrong, because certain B_ID do not have a 1 or 2 on some lines, but then they do on others :(.

SELECT B_ID
FROM NUMBER
WHERE NUM != 1
AND NUM != 2;

SELECT * FROM NUMBER;
+------+------+------+
| A_ID | B_ID | NUM |
+------+------+------+
| 101 | 101 | 1 |
| 101 | 102 | 2 |
| 101 | 201 | 3 |
| 101 | 301 | 4 |
| 102 | 201 | 2 |
| 103 | 102 | 3 |
| 201 | 101 | 1 |
| 301 | 301 | 2 |
| 401 | 102 | 7 |
| 501 | 102 | 1 |
| 501 | 301 | 3 |
+------+------+------+
11 rows in set (0.00 sec)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-10-18 : 05:05:45

SELECT B_ID
FROM NUMBER
WHERE NUM NOT IN (1 ,2);

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-10-18 : 05:13:01

Try these..
DECLARE @emp TABLE ( A_ID INT, B_ID int, NUM INT)
insert @Emp
select 101, 101 , 1 union all
select 101, 102 , 2 union all
select 101, 201 , 3 union all
select 101, 301 , 4 union all
select 102, 201 , 2 union all
select 103, 102 , 3 union all
select 201, 101 , 1 union all
select 301, 301 , 2 union all
select 401, 102 , 7 union all
select 501, 102, 1 union all
select 501, 301, 3

SELECT * FROM @EMP
EXCEPT
SELECT * FROM @EMP WHERE NUM IN ( 1, 2)

SELECT * FROM @EMP WHERE NUM NOT IN ( 1, 2)

If the above is not the solution let us know the expected output for the above sample data

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 08:39:34

I think this is what you're asking for


DECLARE @emp TABLE ( A_ID INT, B_ID int, NUM INT)
insert @Emp 
select 101, 101 , 1 union all
select 101, 102 , 2 union all
select 101, 201 , 3 union all
select 101, 301 , 4 union all
select 102, 201 , 2 union all
select 103, 102 , 3 union all
select 201, 101 , 1 union all
select 301, 301 , 2 union all
select 401, 102 , 7 union all
select 501, 102, 1 union all
select 501, 301, 3 

;With CTE
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY A_ID,B_ID) AS Seq,*
FROM @Emp
)

SELECT c1.*
FROM CTE c1
LEFT JOIN CTE c2
ON c2.Seq = c1.Seq + 1
AND c2.NUM IN (1,2)
WHERE c2.Seq IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

P.Mahendarajah
Starting Member

3 Posts

Posted - 2013-10-18 : 19:53:56

Hey, Thanks for the replies, I ended up finding the solution, it was,

select distinct T.B_ID
from ( select distinct B_ID
from NUMBER
where NUM != 1
and NUM != 2) T
minus
select distinct B_ID
from NUMBER
where NUM < 3
;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-19 : 05:59:28

Keep it simple

-- SwePeso
SELECT		B_ID
FROM		@Emp
GROUP BY	B_ID
HAVING		SUM(CASE WHEN NUM IN (1, 2) THEN 1 ELSE 0 END) = 0

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-19 : 09:41:05

Thats the power of HAVING Clause http://beyondrelational.com/modules/2/blogs/70/posts/19573/efficient-filtering-with-having-clause.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources