Finding a non-existent row in a JOIN
In my query, I match tables based on if there is a matching record in the other table.
SELECT DISTINCT
U.USR_CDE, U.USR_EMAIL, UR.USERROLELISTVALUEID, URg.REGIONLISTVALUEID, GP.RELATEDID, G.GROUPNAME,
GP.PERMISSIONSETID AS GroupPermissionSetId, UP.PERMISSIONSETID AS UserPermissionSetId
FROM TBL_USERS U INNER JOIN
PERM_PERMISSIONSET UP ON U.USR_CDE = UP.RELATEDID AND UP.PERMISSIONSETTYPEVALUEI
PERM_PERMISSIONSETUSERROLE
PERM_PERMISSIONSETREGION URg ON UP.PERMISSIONSETID = URg.PERMISSIONSETID AND
URg.PERMISSIONSETID = UR.PERMISSIONSETID INNER JOIN
PERM_PERMISSIONSETUSERROLE
PERM_PERMISSIONSETREGION GRg ON URg.REGIONLISTVALUEID = GRg.REGIONLISTVALUEID INNER JOIN
PERM_PERMISSIONSET GP ON GR.PERMISSIONSETID = GP.PERMISSIONSETID AND GP.PERMISSIONSETID = GRg.PERMISSIONSETID AND
GP.PERMISSIONSETTYPEVALUEI
PERM_GROUPS G ON GP.RELATEDID = G.GROUPID
WHERE (U.USR_CDE = 1731)
For instance:
INNER JOIN
PERM_PERMISSIONSETREGION GRg ON URg.REGIONLISTVALUEID = GRg.REGIONLISTVALUEID
If the URg exists in the Grg...
But I also want to allow the Grg to have no selections to indicate that ALL Urg's would be okay.
So if Urg's equal 12, 25
and Grg's equal 12,25 that would return two rows.
But if Urg's equal 12,25 and Grg's have no values, meaning none were selected, so it would allow ALL Urg's without having to have a match... how do I write that query?
Th Urg MUST be defined, but an absence of a Grg indicates it should return the row, as any Urg is acceptable.
I of course want to do it for the Roles too...
INNER JOIN
PERM_PERMISSIONSETUSERROLE
Thanks!
SELECT DISTINCT
U.USR_CDE, U.USR_EMAIL, UR.USERROLELISTVALUEID, URg.REGIONLISTVALUEID, GP.RELATEDID, G.GROUPNAME,
GP.PERMISSIONSETID AS GroupPermissionSetId, UP.PERMISSIONSETID AS UserPermissionSetId
FROM TBL_USERS U INNER JOIN
PERM_PERMISSIONSET UP ON U.USR_CDE = UP.RELATEDID AND UP.PERMISSIONSETTYPEVALUEI
PERM_PERMISSIONSETUSERROLE
PERM_PERMISSIONSETREGION URg ON UP.PERMISSIONSETID = URg.PERMISSIONSETID AND
URg.PERMISSIONSETID = UR.PERMISSIONSETID INNER JOIN
PERM_PERMISSIONSETUSERROLE
PERM_PERMISSIONSETREGION GRg ON URg.REGIONLISTVALUEID = GRg.REGIONLISTVALUEID INNER JOIN
PERM_PERMISSIONSET GP ON GR.PERMISSIONSETID = GP.PERMISSIONSETID AND GP.PERMISSIONSETID = GRg.PERMISSIONSETID AND
GP.PERMISSIONSETTYPEVALUEI
PERM_GROUPS G ON GP.RELATEDID = G.GROUPID
WHERE (U.USR_CDE = 1731)
For instance:
INNER JOIN
PERM_PERMISSIONSETREGION GRg ON URg.REGIONLISTVALUEID = GRg.REGIONLISTVALUEID
If the URg exists in the Grg...
But I also want to allow the Grg to have no selections to indicate that ALL Urg's would be okay.
So if Urg's equal 12, 25
and Grg's equal 12,25 that would return two rows.
But if Urg's equal 12,25 and Grg's have no values, meaning none were selected, so it would allow ALL Urg's without having to have a match... how do I write that query?
Th Urg MUST be defined, but an absence of a Grg indicates it should return the row, as any Urg is acceptable.
I of course want to do it for the Roles too...
INNER JOIN
PERM_PERMISSIONSETUSERROLE
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep adding a -2 placeholder for all is working great. thanks!
ASKER
I'm thinking I'm going to need a placeholder for when they want to accept ALL Regions. So when they don't save any Regions, or maybe even have a [Select All] option, it will save a -2 value, so I can check if it is a match or if it contains a -2. But I'm not entirely sure.
There is a user who belongs to a set of roles and set of regions.
There are groups that are assigned to a set of roles and set of regions.
If the user belongs to the same roles and regions that the group belongs to,or if the group does't have a role or region assigned to it, or has a role assigned but not a region or visa versa, then the user belongs to that group.
My query handles when the group has the same roles and regions as the user, but doesn't handle when the group doesn't have a role or region assigned.
The first query above returns the same results I already had. The second returns all the user's roles and regions, but does not connect to the group table at all to find the group with no role and region defined.
If I put in a -2 placeholder if the group accepts ALL, I could change the query like so:
INNER JOIN
PERM_PERMISSIONSETUSERROLE
INNER JOIN
PERM_PERMISSIONSETREGION GRg ON (URg.REGIONLISTVALUEID = GRg.REGIONLISTVALUEID OR GR.USERROLELISTVALUEID = -2)
Would that work?