We help IT Professionals succeed at work.

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.PERMISSIONSETTYPEVALUEID = 378 INNER JOIN
                      PERM_PERMISSIONSETUSERROLE UR ON UP.PERMISSIONSETID = UR.PERMISSIONSETID INNER JOIN
                      PERM_PERMISSIONSETREGION URg ON UP.PERMISSIONSETID = URg.PERMISSIONSETID AND
                      URg.PERMISSIONSETID = UR.PERMISSIONSETID INNER JOIN
                      PERM_PERMISSIONSETUSERROLE GR ON UR.USERROLELISTVALUEID = GR.USERROLELISTVALUEID INNER JOIN
                      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.PERMISSIONSETTYPEVALUEID = 379 INNER JOIN
                      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 GR ON UR.USERROLELISTVALUEID = GR.USERROLELISTVALUEID

Thanks!

Comment
Watch Question

Commented:
question is little bit confusing,
but try changin your inner join to outer joins as follows;

INNER JOIN
                      PERM_PERMISSIONSETREGION GRg ON URg.REGIONLISTVALUEID = GRg.REGIONLISTVALUEID
to
Right Outer JOIN
                      PERM_PERMISSIONSETREGION GRg ON URg.REGIONLISTVALUEID = GRg.REGIONLISTVALUEID

and
INNER JOIN
                      PERM_PERMISSIONSETUSERROLE GR ON UR.USERROLELISTVALUEID = GR.USERROLELISTVALUEID
to
RIGHT OUTER JOIN
                      PERM_PERMISSIONSETUSERROLE GR ON UR.USERROLELISTVALUEID = GR.USERROLELISTVALUEID
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019
Commented:
Then you need to use LEFT JOIN instead of INNER JOIN and try the query below:

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.PERMISSIONSETTYPEVALUEID = 378 INNER JOIN
                      PERM_PERMISSIONSETUSERROLE UR ON UP.PERMISSIONSETID = UR.PERMISSIONSETID INNER JOIN
                      PERM_PERMISSIONSETREGION URg ON UP.PERMISSIONSETID = URg.PERMISSIONSETID AND
                      URg.PERMISSIONSETID = UR.PERMISSIONSETID LEFT JOIN
                      PERM_PERMISSIONSETUSERROLE GR ON UR.USERROLELISTVALUEID = GR.USERROLELISTVALUEID LEFT JOIN
                      PERM_PERMISSIONSETREGION GRg ON URg.REGIONLISTVALUEID = GRg.REGIONLISTVALUEID LEFT JOIN
                      PERM_PERMISSIONSET GP ON GR.PERMISSIONSETID = GP.PERMISSIONSETID AND GP.PERMISSIONSETID = GRg.PERMISSIONSETID AND
                      GP.PERMISSIONSETTYPEVALUEID = 379 LEFT JOIN
                      PERM_GROUPS G ON GP.RELATEDID = G.GROUPID
WHERE     (U.USR_CDE = 1731)
Commented:
Hi

That is a bit confusing. I'm not going to try to help you with the query but ask a question if you want to combine two different set of queries (with the same fields) if yes I would look into UNION and UNION ALL.

Regards
Emil
Starr DuskkASP.NET VB.NET Developer

Author

Commented:
Neither of those gave me the desired results.
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 GR ON (UR.USERROLELISTVALUEID = GR.USERROLELISTVALUEID OR GR.USERROLELISTVALUEID = -2)
INNER JOIN
                      PERM_PERMISSIONSETREGION GRg ON (URg.REGIONLISTVALUEID = GRg.REGIONLISTVALUEID OR GR.USERROLELISTVALUEID = -2)
Would that work?
Starr DuskkASP.NET VB.NET Developer

Author

Commented:
Yep adding a -2 placeholder for all is working great. thanks!