joins or subqueries - confused

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)

joins or subqueries - confused

Author

Topic

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-03-25 : 14:44:21

Hi all,

right now am using sub queries for the below query.. but is this a good way or do i need to use joins.. however if you see the "downloads" column i need to get a count of how many download does each user did on the files/url.

SELECT
UU_Title as Title,
FileName_Url = (Case When UU_Type = 'U' Then UU_url else UU_Filename End),
UU_UserID as UserID,
B.User_FirstName + ' ' + B.User_LastName AS Author,
UU_TimeStamp as DateCreated,
UU_LastModified as DateModified,
Downloads = (select count(*) from Vportaldevstats.[dbo].stats_download A where A.stat_AuID = UU_AssocAuID ),
Presentation_Associations = (Select Presentation_Association = (Case When au_subject is Null or au_subject != '' then au_title else au_title end) from VPortalDev.[dbo].au A where a.Au_id = UU_AssocAuID),
Community_Associations = (Select community_Association = (Case When au_subject is Null or au_subject != '' then au_title else au_title end) from VPortalDev.[dbo].au A where a.Au_id = UU_AssocCommunityID)
From Vportaldev.[dbo].user_upload A , Vportaldev.[dbo].[User] B
Where A.UU_UserID = B.User_ID
Order by UU_LastModified Desc

and i tried re-modifying the above query as joins.. just wanna know is this the best way to do it. (and i don't know how to handle count(*) downloads .. pls.help.

SELECT
UU_Title as Title,
FileName_Url = (Case When UU_Type = 'U' Then UU_url else UU_Filename End),
UU_UserID as UserID,
B.User_FirstName + ' ' + B.User_LastName AS Author,
UU_TimeStamp as DateCreated,
UU_LastModified as DateModified,
COUNT(*)

from
Vportaldev.[dbo].user_upload uu
Innerjoin Vportaldev.[dbo].[user] u
ON UU.UU_USERID = U.USER_ID
INNERJOIN Vportaldevstats.[dbo].stats_download SD
ON UU.UU_ASSOCAUID = SD.STAT_AUID
INNERJOIN VPortalDev.[dbo].au AU
ON AU.Au_id = UU.UU_ASSOCAUID

any help is much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-25 : 14:52:13

[code]SELECT
UU_Title as Title,
FileName_Url = (Case When UU_Type = 'U' Then UU_url else UU_Filename End),
UU_UserID as UserID,
B.User_FirstName + ' ' + B.User_LastName AS Author,
UU_TimeStamp as DateCreated,
UU_LastModified as DateModified,
t.DownloadCount

from
Vportaldev.[dbo].user_upload uu
Innerjoin Vportaldev.[dbo].[user] u
ON UU.UU_USERID = U.USER_ID
INNERJOIN Vportaldevstats.[dbo].stats_download SD
ON UU.UU_ASSOCAUID = SD.STAT_AUID
INNERJOIN VPortalDev.[dbo].au AU
ON AU.Au_id = UU.UU_ASSOCAUID
INNER JOIN(select stat_AuID,count(*) as DownloadCount
from Vportaldevstats.[dbo].stats_download A
group by A.stat_AuID
)t
ON t.stat_AuID= UU_AssocAuID[/code]

Also i think its always best to use joins other than subqueries especially when record number is huge.

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-03-25 : 15:18:37

Hi Visakh,

am getting syntax error at this line.. (changed from INNER JOIN to InnerJoin) but stil i get this syntax error.

INNERJOIN (select stat_AuID,count(*) as DownloadCount
from Vportaldevstats.[dbo].stats_download A
group by A.stat_AuID
) t

stephe40
Posting Yak Master

218 Posts

Posted - 2008-03-25 : 15:23:00

When using sub selects SQL Server compiles the query using join operations anyway. In this cause just look at the execution plan for each alternative and you will probably see that the same plan is used for both queries.

- Eric

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-25 : 15:24:08

put a space between its two words INNER JOIN not INNERJOIN

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-03-25 : 15:32:13

put a space between its two words INNER JOIN not INNERJOIN

for all Inner Joins or just this.. (i tried with INNER JOIN) just for this and doesn't work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-25 : 15:47:53

all.why such a question?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources