select help - SQL Server Forums

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 help

Author

Topic

iradev
Starting Member

45 Posts

Posted - 2010-07-02 : 11:31:47

I have a table called AppActions with some sample data below. Basically I want to select AppId's that dont have a StatusId of 10 or 40. If I do a select where AppActions.StatusId<>10 AND AppActions.StatusId<>40 I get the rows for AppId 1 that don't contain 10 or 40. I want the query to return only AppId 2 once and thats it. Is that possible?

AppActionID | AppId | StatusId
1 | 1 | 20
2 | 1 | 10
3 | 1 | 30
4 | 1 | 40
5 | 2 | 20
6 | 2 | 30
7 | 2 | 20
8 | 2 | 30

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-02 : 11:47:20

select distinct AppId from AppActions where AppId not in
(
select AppId from AppActions where StatusId in (10,40)
)

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-02 : 11:48:13

[code]

CREATE TABLE #myTable99(AppActionID int, AppId int, StatusId int)
GO

INSERT INTO #myTable99(AppActionID, AppId, StatusId)
SELECT 1 , 1 , 20 UNION ALL
SELECT 2 , 1 , 10 UNION ALL
SELECT 3 , 1 , 30 UNION ALL
SELECT 4 , 1 , 40 UNION ALL
SELECT 5 , 2 , 20 UNION ALL
SELECT 6 , 2 , 30 UNION ALL
SELECT 7 , 2 , 20 UNION ALL
SELECT 8 , 2 , 30
GO

SELECT DISTINCT AppId
FROM #myTable99 o
WHERE NOT EXISTS (SELECT * FROM #myTable99 i
WHERE StatusId In (10,40)
AND i.AppId = o.AppId)
GO

DROP TABLE #myTable99
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources