Incorrect syntax near the keyword ON in JOIN-CodePudding

Could anyone assist with this error? Someone else showed me how to create a join and as far as I can tell I duplicated it exactly, but with another table but I can't get it to work:

SELECT [ITEMID]
  ,[ITEMDATAAREAID]
  ,ITEM.[ITEMGROUPID]
  ,[ITEMGROUPDATAAREAID]
FROM [ELG_DynamicsAX_DWH].[dbo].[INVENTITEMGROUPITEM] AS ITEM
Join(Select[NAME] AS [ITEMGROUPNAME]
  ,[ITEMGROUPID]
FROM[ELG_DynamicsAX_DWH].[dbo].[INVENTITEMGROUP] AS INVENTGROUP ON INVENTGROUP.ITEMGROUPID = 
ITEM.ITEMGROUPID

Thank you for your help!

CodePudding user response：

SELECT 
   [ITEMID]
  ,[ITEMDATAAREAID]
  ,ITEM.[ITEMGROUPID]
  ,INVENTGROUP.[NAME] AS [ITEMGROUPNAME]
  ,[ITEMGROUPDATAAREAID]
FROM 
   [ELG_DynamicsAX_DWH].[dbo].[INVENTITEMGROUPITEM] AS ITEM
   Join [ELG_DynamicsAX_DWH].[dbo].[INVENTITEMGROUP] AS INVENTGROUP 
      ON INVENTGROUP.ITEMGROUPID = ITEM.ITEMGROUPID

CodePudding user response：

SELECT [ITEMID]
      ,[ITEMDATAAREAID]
     ,ITEM.[ITEMGROUPID]
     ,[ITEMGROUPDATAAREAID]
     X.[ITEMGROUPNAME]
FROM [ELG_DynamicsAX_DWH].[dbo].[INVENTITEMGROUPITEM] AS ITEM
Join
(
   Select[NAME] AS [ITEMGROUPNAME]
     ,[ITEMGROUPID]
   FROM[ELG_DynamicsAX_DWH].[dbo].[INVENTITEMGROUP] AS INVENTGROUP
)X ON X.ITEMGROUPID =ITEM.ITEMGROUPID

Something like this