help with query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

help with query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-02-27 : 20:26:19

Hi,

I have the following query, which is working fine, but I wanted to add some more advanced functionality to it.

I have posted the current query and table structure below.

What I want to do is to modify the query, so it returns 1 extra column. This column is going to be a char value of "Y" or "N".

Currently each row that is brought back by this query represents the "viewerUserID" viewing the "userID" columns. This extra column I want to add should represent whether the "userID" has viewed the "viewerUserID".

Hopefully thats clear. I have a 185,418,669 rows in this table as of now, so I need to be really careful on doing this an efficient way.

Any help is greatly appreciated.

Thanks once again!
mike123

SELECT TOP 54 viewerUserID,viewerNameOnline,MAX(viewDate) AS max_viewDate
FROM (

SELECT viewerUserID,UD.nameOnline as viewerNameOnline, viewDate

FROM tblProfileViews_Users PVU

JOIN tblUserDetails UD on PVU.viewerUserID = UD.userID

WHERE profileUserID = @userID AND ( UD.active = 1 or UD.active = 4) and visible = 1

) d

GROUP BY viewerUserID ,viewerNameOnline
ORDER BY max_viewDate DESC

Table Structure:

CREATE TABLE [dbo].[tblProfileViews_Users](
[viewID] [int] IDENTITY(1,1) NOT NULL,
[viewerUserID] [int] NOT NULL,
[profileUserID] [int] NOT NULL,
[viewDate] [smalldatetime] NOT NULL,
[visible] [tinyint] NULL
) ON [PRIMARY]

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-28 : 06:55:10

Something similar to this

SELECT TOP 54	pvu.viewerUserID,
		UD.nameOnline as viewerNameOnline,
		MAX(pvu.viewDate) AS max_viewDate,
		CASE
			WHEN EXISTS (SELECT * FROM tblProfileViews_Users AS x WHERE x.profileUserID = pvu.viewerUserID AND x.viewerUserID = @userID) THEN 'Y'
			ELSE 'N'
		END AS Mike123Special
FROM		tblProfileViews_Users AS PVU
INNER JOIN	tblUserDetails AS UD on PVU.viewerUserID = UD.userID
WHERE		pvu.profileUserID = @userID
		AND UD.active IN (1, 4)
		and pvu.visible = 1
GROUP BY	pvu.viewerUserID,
		UD.nameOnline
ORDER BY	MAX(pvu.viewDate) DESC