SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SELECT WHERE NUMBER IS NEVER 1 OR 2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

P.Mahendarajah
Starting Member

3 Posts

Posted - 10/18/2013 :  04:09:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 10/18/2013 :  05:05:45  Show Profile  Reply with Quote
SELECT B_ID
FROM NUMBER
WHERE NUM NOT IN (1 ,2);

--
Chandu

Edited by - bandi on 10/18/2013 05:08:10
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 10/18/2013 :  05:13:01  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/18/2013 :  08:39:34  Show Profile  Reply with Quote
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
Go to Top of Page

P.Mahendarajah
Starting Member

3 Posts

Posted - 10/18/2013 :  19:53:56  Show Profile  Reply with Quote
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
;

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 10/19/2013 :  05:59:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 10/19/2013 :  09:41:05  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000