removing records / filtering from temp table

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 2005 Forums

Transact-SQL (2005)

removing records / filtering from temp table

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-25 : 19:32:13

Hi,

I am populating a TEMP TABLE with results combined from 3 tables. I want to filter the results put into this table, and I am not sure the best way to do it.

The way I want to filter the results is so that we do not have records from the table [tblStatusUpdates_BlockedUsers] WHERE userID = @userID.

Now to do this I am not sure if I should run a WHERE NOT IN on each of the 3 select statements, or if I should populate the temp table as is, then run a DELETE against the temp table (not sure thats even possible)

I'm just trying to be as efficient as possible and avoid running it 3 x unnecessarily.

Any input is greatly appreciated!

thanks again!

,
mike123

DECLARE @Friends TABLE
(
friendID INT PRIMARY KEY CLUSTERED,
nameOnline VARCHAR(15)
)

INSERT @Friends
(
friendID,
nameOnline
)

-- GET FRIENDS TABLE
SELECT f.friendID,
u.nameOnline
FROM tblFriends AS f
INNER JOIN tblUserDetails AS u ON u.userID = f.friendID
WHERE f.userID = @userID AND f.friendID <> @userID

UNION

-- GET HOTLIST TABLE
SELECT h.hotUserID,
u.nameOnline
FROM tblHotList AS h
INNER JOIN tblUserDetails AS u ON u.userID = h.hotUserID
WHERE h.userID = @userID AND h.hotUserID <> @userID

UNION

-- GET PHOTO NOTIFICATIONS TABLE
SELECT pn.friendID,
u.nameOnline
FROM tblphoto_notifications AS pn
INNER JOIN tblUserDetails AS u ON u.userID = pn.friendID
WHERE pn.userID = @userID

--this is the query that brings back results we want to filter.

SELECT [blocked_userID] FROM [dbo].[tblStatusUpdates_BlockedUsers] WHERE userID = @userID

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-03-26 : 05:19:15

I think you can use another var @blocked
select @blocked=...
and then use this var in your query.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-03-26 : 05:20:33

or you can LEFT JOIN tblStatusUpdates_BlockedUsers ON ...
and check if blocked_userID is NULL in WHERE-CLAUSE

No, you're never too old to Yak'n'Roll if you're too young to die.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-26 : 18:54:12

quote:
Originally posted by webfred

or you can LEFT JOIN tblStatusUpdates_BlockedUsers ON ...
and check if blocked_userID is NULL in WHERE-CLAUSE

No, you're never too old to Yak'n'Roll if you're too young to die.

yes I think this would work too..... any idea which is better, this option or a WHERE NOT IN (SELECT ...) type clause?

Thanks again!
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-26 : 19:00:15

Joins are typically better than subqueries. To see which one works better in your situation, compare the execution plans and run both solutions in the same batch to see which one is more efficient. My bet is on the join being faster, even if it's minimal.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-26 : 20:11:50

Hi Tara,

Thanks for the tip, I will try your suggestion, much appreciated !

cheers :)
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-27 : 00:17:18

You're welcome, let us know what your tests show.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources