can't get a 'Not In' to work properly

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

SQL Server 2000 Forums

SQL Server Development (2000)

can't get a 'Not In' to work properly

Author

Topic

mcbucho
Starting Member

2 Posts

Posted - 2002-08-19 : 16:51:44

Probably remedial, but...

I would like to find what a user does NOT have selected from a set of 'portletID's. With my existing query, i get all of the unselected items so long as someone else has the items selected that they're missing (wierd). PLEASE HELP! Thank you!

Here's what I have:

SELECT distinct
tblPortalUserPortlets.fkPortletID,

tblVMNApplets.vcDescription

FROM
tblPortalUserPortlets
INNER JOIN
tblVMNApplets ON tblVMNApplets.intPortletID = tblPortalUserPortlets.fkPortletID
WHERE
tblVMNApplets.intPortletID not in (select intPortletID from tblVMNApplets INNER JOIN
tblPortalUserPortlets ON tblVMNApplets.intPortletID = tblPortalUserPortlets.fkPortletID WHERE
tblPortalUserPortlets.fkUserID = '42239')
ORDER by
tblPortalUserPortlets.fkPortletID

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-08-19 : 19:50:41

maybe I'm missing something but it worked fine for me...but you don't need to do the join in the first part of the query - it's superfluous....

change
SELECT distinct a.fkPortletID, b.vcDescription
FROM
@PortalUserPortlets a INNER JOIN @VMNApplets b
ON a.fkPortletID = b.intPortletID
WHERE
b.intPortletID not in
to
SELECT distinct intPortletID, vcDescription
FROM @VMNApplets
WHERE intPortletID not in

and it will work the same....

here's my test script
[b]declare @PortalUserPortlets table (fkPortletID int, fkUserId nvarchar(50))
declare @VMNApplets table (intPortletID int, vcDescription nvarchar(100))

insert into @VMNApplets values (1, 'test1a')
insert into @VMNApplets values (1, 'test1b')
insert into @VMNApplets values (2, 'test2a')
insert into @VMNApplets values (2, 'test2b')
insert into @PortalUserPortlets values (1, '42239')
insert into @PortalUserPortlets values (2, '42240')

SELECT distinct intPortletID, vcDescription
FROM @VMNApplets
WHERE intPortletID not in
(select intPortletID from @VMNApplets a INNER JOIN @PortalUserPortlets b
ON a.intPortletID = b.fkPortletID
WHERE b.fkUserID = '42239')
ORDER by intPortletID

Hope that helps

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

mcbucho
Starting Member

2 Posts

Posted - 2002-08-20 : 08:20:22

Thank you VERY much for your help, it works perfectly!!!
-Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources