Why does this NOT IN statement not work

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

Why does this NOT IN statement not work

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-04-20 : 23:00:20

Hello,

This query should return results, minus any rows that have a UserId in a Filter table (which is just two columns, one the userId and the other a filteredUserId that the user has chosen to block)


alter procedure sp_wm_GetAds

@userid int

as
select 
	a.*, 
	dbo.GetAge(a.bday, GETDATE()) age 
from 
	wm_user a 
where 
	hasimage=1 and
	a.userid not in 
		(select userid from wm_filter where userid=@userid and filteredUserId=a.userid)

order by nickname

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-21 : 01:10:09

[code]
select
a.*,
dbo.GetAge(a.bday, GETDATE()) age
from
wm_user a left join wm_filter f
on a.userid = f.filteredUserId
where f.userid = @userid
and a.hasimage = 1
and f.filteredUserId is null
[/code]

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-04-21 : 03:08:32

Hi Thanks for the reply.

The f.userid or FilteredUserId is the user id's that need to be excluded from the list, that is why I was trying to use a not in clause. This query returned no results....

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-21 : 05:43:24

Please post some sample data and the result that you want.

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-04-21 : 06:02:09

OK - here is some sample data from my tables:

I want to get a list of users from this table:

WM_USER

UserId | HasImage
1 1
23 1
53 1
46 1

UNLESS that userId exists in the wm_filter table

WM_FILTER

userId | filteredUserId
134 53

So in this case, if the userId in the @userid param is 134, it would show all the users in the WM_USER table EXCEPT for 53, since that numeric combination exists in the wm_filter table.

I hope that makes sense. Thanks again for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-21 : 06:10:18

[code]
select
a.*,
dbo.GetAge(a.bday, GETDATE()) age
from
wm_user a left join wm_filter f
on a.userid = f.filteredUserId
~~where~~and f.userid = @userid
~~and~~where a.hasimage = 1
and f.filteredUserId is null
[/code]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-04-23 : 09:03:35

Also, try renaming your proc. Starting it with SP_ tells SQL SERVER to look in the MASTEER DB for the proc first, no matter what. (At least, it did in 2K. Not sure with 2K5.)

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-04-23 : 11:48:10

Thanks KH - that worked perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources