Search code examples
sql-serverleft-joininner-joinssmsdotnetnuke

How do I get latest versions of modules in my left inner join?

I am using a SQL left inner join to query 4 tables. One of the tables HtmlText contains both ModuleID and Version columns. What I want to accomplish is pull only the MAX version of every ModuleID from a specific site or PortalID. Here is what I have tried

SELECT TBS.PortalID [PortalID], TBS.TabID [TabID], TBS.TabName [TabName], 
TBS.TabPath [TabPath], HTM.Version[Version], TBM.ModuleID [ModuleID],     
MDS.ModuleID[ModuleID], HTM.Content[Content]
FROM [MyDB].[dbo].[Tabs] TBS
Inner JOIN [MyDB].[dbo].[Modules] MDS 
LEFT JOIN [MyDB].[dbo].[TabModules] TBM
LEFT JOIN [MyDB].[dbo].[HtmlText] HTM 
ON HTM.[ModuleID] = TBM.[ModuleID]
ON MDS.[ModuleID] = TBM.[ModuleID]
ON TBS.[TabID] = TBM.[TabID] 
WHERE TBS.[PortalID] = '0' AND DataLength(HTM.[Content]) <> 0  
AND Version = (Select MAX([Version]) from [MyDB].[dbo].[HtmlText])

But this only gives me the ModuleID with the largest Version, instead of the MAX Version of all of the different ModuleIDs

enter image description here

Solution

  • Try changing your last predicate to this: 

    AND Version = (Select MAX([Version]) from [MyDB].[dbo].[HtmlText] where ModuleId = HTM.ModuleId)

    This will get the max version for that particular module