Correct connection does not return null values
My tables:
allowed(resourceid, personid) person(personid, email)
I want to print if a person has permission to access a resource, say with resourceid = 2. The result should be:
personright(personid, email, resourceid)
and resourceid should be empty if the person is not allowed access to resource 2. If the person is allowed access 2, the resource should be 2.
So I expect that every time my request is executed, the entire list of users is printed.
I had a working solution using a subquery, but I want to do it using a join.
select person.personid, person.email, allowed.resourceid
from person
right join allowed on(person.personid = allowed.personid)
where (allowed.resourceid IS NULL OR allowed.resourceid = 2);
Why doesn't it work?
source to share
Depending on your description of the problem - there should be a left join , not a right join.
select person.personid, person.email, allowed.resourceid
from person
left join allowed on person.personid = allowed.personid and allowed.resourceid = 2
Also note that I have moved the condition allowed.resourceid = 2
from the clause where
to the join condition. Thus, if allowed
there are no matching records in the table, the table resourceid = 2
and personid
is equal to the corresponding personid
from table person
, you will get zero as allowed.resourceid
exactly as needed.
source to share