Convert TSQL query to MS SQL query

[accessNator]

I am trying to convert this query I have in TSQL. I am running into problems on the proper syntax. This is the original syntax.

SELECT     
    u.UserID, 
    u.FirstName, 
    u.LastName, 
    u.Username, 
    u.Email, 
    u.DisplayName, 
    up1.PropertyValue AS TypeOfAccess
FROM
    dbo.dnn_users AS u INNER JOIN
    dbo.dnn_UserRoles AS ur ON u.UserID = ur.UserID INNER JOIN
    dbo.dnn_Roles AS r ON ur.RoleID = r.RoleID AND r.RoleName = N'Agent' LEFT OUTER JOIN
    (SELECT     
        up.UserID, 
        up.PropertyValue
    FROM
        dbo.dnn_UserProfile AS up INNER JOIN
        dbo.dnn_ProfilePropertyDefinition AS ppd ON 
        up.PropertyDefinitionID = ppd.PropertyDefinitionID AND 
        ppd.PropertyName = 'TypeOfAccess' AND 
        ppd.PortalID = 0) AS up1 ON u.UserID = up1.UserID

So far, I think I have it converted to the following, ,but Im still having problems with the proper joins and parenthesis.

SELECT     
    u.UserID, 
    u.FirstName, 
    u.LastName, 
    u.Username, 
    u.Email, 
    u.DisplayName, 
    up1.PropertyValue AS TypeOfAccess
FROM
    (dbo.dnn_users AS u INNER JOIN
    dbo.dnn_UserRoles AS ur ON u.UserID = ur.UserID) INNER JOIN
    dbo.dnn_Roles AS r ON ur.RoleID = r.RoleID AND r.RoleName = "Agent" LEFT OUTER JOIN
    (SELECT     
        up.UserID, 
        up.PropertyValue
    FROM
        dbo.dnn_UserProfile AS up INNER JOIN
        dbo.dnn_ProfilePropertyDefinition AS ppd ON 
        up.PropertyDefinitionID = ppd.PropertyDefinitionID AND 
        ppd.PropertyName = "TypeOfAccess" AND 
        ppd.PortalID = 0) AS up1 ON u.UserID = up1.UserID

I would appreciate any assistance.

Thanks.

 

[Guus2005]

You could continue trying to make the sql statement work but you can also split the problem in smaller pieces.

Remove the subquery from the main query and save in as qryUP1

SELECT     
        up.UserID, 
        up.PropertyValue
    FROM
        dbo.dnn_UserProfile AS up INNER JOIN
        dbo.dnn_ProfilePropertyDefinition AS ppd ON 
        up.PropertyDefinitionID = ppd.PropertyDefinitionID AND 
        ppd.PropertyName = 'TypeOfAccess' AND 
        ppd.PortalID = 0

The remainder of the main query save it under a different name e.g. qryTSQL.

SELECT     
    u.UserID, 
    u.FirstName, 
    u.LastName, 
    u.Username, 
    u.Email, 
    u.DisplayName, 
FROM
    dbo.dnn_users AS u INNER JOIN
    dbo.dnn_UserRoles AS ur ON u.UserID = ur.UserID INNER JOIN
    dbo.dnn_Roles AS r ON ur.RoleID = r.RoleID AND r.RoleName = "Agent"

Now create a new LEFT OUTER JOIN query to combine the two queries together thus creating the final result.

It is easier to read and easier to maintain.

HTH

 

[accessNator]

Guus2005,
Thanks you for your reply. I did attempt the method but unfortunately, when trying to translate it into two separate queries as MS SQL syntax instead of TSQL, I keep doing something wrong.

 

[Guus2005]

Please post a sample database showing the error.