SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Just want to understand
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vijays3
Constraint Violating Yak Guru

India
338 Posts

Posted - 03/10/2014 :  04:49:03  Show Profile  Reply with Quote
Hi All,
Just want to know that is it good practice to create multiple INNER JOIN on derived tables as I have written below or use a #temp table
for all derived tables and use them into JOIN. Please advise .
This below query is also very hard to understand what is going on .


CREATE TABLE #Temp
(
    NumPlayers INT, 
    ModuleID INT, 
    ClientId INT,
    ASF_Version VARCHAR(10),
    ASF_VersionHead INT
);

INSERT INTO #Temp

SELECT NumPlayers=Count( DISTINCT UserId), ModuleID, ClientId, ASF_Version, ASF_VersionHead

FROM (SELECT t.UserID, 
             m.ModuleID, 
             m.ClientID, 
             t.UserTransNumber, 
             t.EventNumber, 
             t.GameData, 
             CAST(t.GameData AS XML).value('(/X/@version)[1]', 'varchar(50)') AS ASF_Version,
	           
             (SELECT value FROM fn_SplitDelimitedList(Cast(t.Gamedata AS XML).value('(/X/@version)[1]', 'varchar(50)'), '.') WHERE idx = 0) AS ASF_VersionHead
             
	      FROM (SELECT ugd.UserID, 
                     Max(EventNumber) AS max_EventNumber, 
                     cg.ModuleID, 
                     cg.ClientID
			           FROM dbo.tb_UserGameData ugd (nolock) 

                 JOIN ( SELECT UserID, 
                              ModuleID, 
                              ClientID, 
                              TransNumber
					               FROM dbo.tb_CurrentGame (nolock)) cg 
				           
                   ON cg.UserId = ugd.UserId 
                  AND cg.TransNumber = ugd.UserTransNumber 
             GROUP BY ugd.UserID, cg.ModuleID, cg.ClientID) AS m
	               JOIN dbo.tb_UserGameData AS t 
                   ON t.UserID = m.UserID 
                  AND t.EventNumber = m.max_EventNumber) AS x
    
    GROUP BY ModuleID, ClientId, ASF_Version, ASF_VersionHead
    HAVING ASF_VersionHead < 1000
    ORDER BY ModuleID, ASF_Version

DELETE FROM #Temp
WHERE ModuleId IN (Select ModuleID from #temp where ASF_VersionHead > 130 )

SELECT * FROM #Temp

IF(OBJECT_ID('tempdb..#Temp') Is Not Null)
BEGIN
    DROP TABLE #Temp
END


Vijay is here to learn something from you guys.

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/10/2014 :  15:28:45  Show Profile  Reply with Quote
Hi Vijay,
In the above query, a temporary table is created. It is filled with the values that are derived using multiple joins (i.e. joining multiple tables).

=======================
Not an Expert, Just a learner.
!_(M)_!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000