Query my users files only - 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 2000 Forums

SQL Server Development (2000)

Query my users files only

Author

Topic

danjapro
Starting Member

44 Posts

Posted - 2008-04-15 : 10:38:02

This Query is returing all the Users information and more too much.
I need to only return the information that is ispecfici within that date for that particular user not tons (1,000 of records) of data. It should only be maye hundreds. HELP ME WITH QUERY PLEASE....
Query


SELECT DISTINCT 
                      dbo.UserInfo.tp_SiteID AS SiteID, dbo.UserInfo.tp_GUID AS GUID, dbo.UserInfo.tp_ID AS ID, dbo.UserInfo.tp_Deleted AS FilesDeleted, 
                      dbo.UserInfo.tp_Login, dbo.UserInfo.tp_Title AS Name, dbo.UserInfo.tp_Notes AS Extra, dbo.UserData.tp_Editor, 
                      dbo.UserData.tp_Modified AS DateModified, dbo.Docs.DirName, dbo.Docs.LeafName, dbo.Docs.ListId, dbo.Docs.Type, dbo.Docs.TimeLastModified, 
                      dbo.Docs.Extension
FROM         dbo.Docs LEFT OUTER JOIN
                      dbo.UserData ON dbo.Docs.SiteId = dbo.UserData.tp_SiteId LEFT OUTER JOIN
                      dbo.UserInfo ON dbo.UserData.tp_SiteId = dbo.UserInfo.tp_SiteID
WHERE     (dbo.UserInfo.tp_Login = 'HISNT\sfehn') AND (dbo.UserInfo.tp_GUID = dbo.UserInfo.tp_GUID) AND (dbo.UserData.tp_Modified BETWEEN 
                      '2008-01-01' AND '2008-04-04')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 12:10:28

Why do you think its retrieving more data? Also whats the purpose of this redundant filter?

(dbo.UserInfo.tp_GUID = dbo.UserInfo.tp_GUID)

danjapro
Starting Member

44 Posts

Posted - 2008-04-15 : 14:53:23

Removed that where clause with the dbo.UserIngo.Guid. But I still get over 1,000 records returning within a shortened date range.

PLEASE HELP

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-15 : 15:57:31

It looks like you can change your OUTER joins to INNER joins as you have affectively made INNER joins by adding your WHERE clause. Is there another link between UserInfo and UserData, like a UserID?Without any sample data it might be kind of hard to diagnose, but try this maybe?

SELECT DISTINCT 
	dbo.UserInfo.tp_SiteID AS SiteID, 
	dbo.UserInfo.tp_GUID AS GUID, 
	dbo.UserInfo.tp_ID AS ID, 
	dbo.UserInfo.tp_Deleted AS FilesDeleted, 
	dbo.UserInfo.tp_Login, 
	dbo.UserInfo.tp_Title AS Name, 
	dbo.UserInfo.tp_Notes AS Extra, 
	dbo.UserData.tp_Editor, 
	dbo.UserData.tp_Modified AS DateModified, 
	dbo.Docs.DirName, 
	dbo.Docs.LeafName, 
	dbo.Docs.ListId, 
	dbo.Docs.Type, 
	dbo.Docs.TimeLastModified, 
	dbo.Docs.Extension
FROM      
	dbo.UserInfo 
INNER JOIN
	dbo.UserData 
	ON dbo.UserData.tp_SiteId = dbo.UserInfo.tp_SiteID
	AND dbo.UserInfo.tp_Login = 'HISNT\sfehn'
	AND dbo.UserData.tp_Modified BETWEEN '2008-01-01' AND '2008-04-04'
INNER JOIN
	dbo.Docs 
	ON dbo.Docs.SiteId = dbo.UserData.tp_SiteId

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 16:21:48

quote:
Originally posted by visakh16

dbo.UserInfo.tp_GUID = dbo.UserInfo.tp_GUID

To discard all records where tp_GUID is NULL.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources