none
SQL Query to detect non-existence of certain records

    Question

  •  

    Here is my query:

    SELECT     dnn_Roles.RoleName, xyzUser.FirstName, xyzUser.LastName, xyzUser.Email, xyzRewardPoint.Points, xyzRewardPoint.RewardID
    FROM         xyzRewardPoint INNER JOIN
                          xyzUser ON xyzRewardPoint.UserID = xyzUser.UserId INNER JOIN
                          dnn_UserRoles INNER JOIN
                          dnn_Roles ON dnn_UserRoles.RoleID = dnn_Roles.RoleID ON xyzUser.ProviderId = dnn_UserRoles.UserID
    WHERE     (dnn_UserRoles.RoleID = 3) OR
                          (dnn_UserRoles.RoleID = 4) OR
                          (dnn_UserRoles.RoleID = 6)
    ORDER BY dnn_UserRoles.RoleID

    What I need is to extend this query to detect any users who exist in dnn_UserRoles.RoleID 3, 4 or 6 but do not have a RewardID value of '43' in the xyzRewardPoint table.
    Thursday, December 27, 2007 10:03 PM

Answers

All replies

  • So, if I understand your requirements correctly; you want to list all users with RoleID 3, 4, or 6 except those with RewardID value '43'.   If this is correct, then try the following:

     

    Code Block

    SELECT     dnn_Roles.RoleName, xyzUser.FirstName, xyzUser.LastName, xyzUser.Email, xyzRewardPoint.Points, xyzRewardPoint.RewardID
    FROM         xyzRewardPoint INNER JOIN
                          xyzUser ON xyzRewardPoint.UserID = xyzUser.UserId INNER JOIN
                          dnn_UserRoles INNER JOIN
                          dnn_Roles ON dnn_UserRoles.RoleID = dnn_Roles.RoleID ON xyzUser.ProviderId = dnn_UserRoles.UserID
    WHERE     dnn_UserRoles.RoleID IN ('3','4','6')

          And      xyzRewardPoint.RewardID <> '43'
    ORDER BY dnn_UserRoles.RoleID

     

     

     

     

     

    Thursday, December 27, 2007 10:19 PM
  •  

    The requirement is exactly this:

    I need to detect any users who are in dnn_UserRoles.RoleID 3, 4 or 6 but do not have a RewardID value of '43' in the xyzRewardPoint table.
    Thursday, December 27, 2007 10:45 PM
  • Did you try the query I provided?  It should meet your requirements.

     

     

    Thursday, December 27, 2007 10:56 PM