We help IT Professionals succeed at work.
Get Started

Finding a non-existent row in a JOIN

241 Views
Last Modified: 2012-05-09
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
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 3 Answers and 5 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE