0

In my application I have a table "user", a table "role", and a table "userroles". Userroles is simple table consisting of user, role, where user corresponds to the id column of the user table. As user can have multiple roles, and obviously multiple users can have the same role. Now I wish to find all users who do NOT have the specific role (private_account), and I wish to list them as (user_id, new-inserted-role-id) for further processing.

SELECT u.id, r.id
FROM public."user" AS u
WHERE u.id NOT IN (
    SELECT ur.user
    FROM public."userroles" as ur
    INNER JOIN public."role" as r
    ON ur.role=r.id
    WHERE r."roleDescription"='private_account'
)

However this complains that the table r does not exist.

ERROR: missing FROM-clause entry for table "ur"

How can I do this?

So for clarity: if private_account would have id 2 and I a user 1 which does not have this ID the return would look like:

USER | role
1    | 2
2    | 2

The role reported is independent on what the user actually has. I could do this manually and do a select like below. However this has the huge disadvantage that I have to manually update the sql if I move to a different database (since the ID might not correspond to the actual role name).

SELECT u.id, 2
...

The further processing is basically using the return from the select as INSERT INTO:

INSERT INTO public."userroles"
SELECT u.id, r.id
FROM public."user" AS u
WHERE u.id NOT IN (
    SELECT ur.user
    FROM public."userroles" as ur
    INNER JOIN public."role" as r
    ON ur.role=r.id
    WHERE r."roleDescription"='private_account'
)
  • What is "actual_role_id" supposed to be? Each role that each qualifying user does have, or just the role id for 'private_account' (which is the one they don't have, in order to meet the qualification)? – jjanes Aug 23 at 17:07
  • @jjanes the role they don't have - just so I can insert it as a new role. – paul23 Aug 23 at 20:03
0

Create tables:

postgres=# create table "user" (
    id int primary key,
    name text unique);
postgres=# create table "role" (
    id int primary key,
    name text unique);
postgres=# create table "userroles" (
    "user" int references "user",
    "role" int references "role",
    primary key("user", "role")
);

Populate test data

postgres=# insert into "user" values (1, 'privateuser'), (2, 'publicuser');
postgres=# insert into "role" values (1, 'private_account'), (2, 'other_role');
postgres=# insert into userroles values (1,1), (1,2), (2,2);

Find all users and their roles:

postgres=# SELECT u.id, u.name as "user", r.id, r.name as "role"
    FROM "user" u
    LEFT JOIN "userroles" ur ON ur.user=u.id
    LEFT JOIN "role" r ON r.id=ur.role;

 id |    user     | id |      role       
----+-------------+----+-----------------
  1 | privateuser |  1 | private_account
  1 | privateuser |  2 | other_role
  2 | publicuser  |  2 | other_role
(3 rows)

Find all users who do NOT have the specific role:

postgres=# select u.id, u.name as "user", r.id, r.name as "role"
    FROM "user" u
    LEFT JOIN userroles ur ON ur.user=u.id
    LEFT JOIN "role" r ON r.id=ur.role
    WHERE u.id NOT IN (
        SELECT "user" FROM userroles ur2, "role" r2
        WHERE r2.id=ur2.role AND r2.name='private_account');

 id |    user    | id |    role       
----+------------+----+-------------
  2 | publicuser |  2 | other_role
(1 row)

This last query is your answer.

  • That shows the other_role however I wish to get the id of the private_account role. - So I can then use that result to insert back into the database and give all users which have not yet private account role the private account role. – paul23 Aug 23 at 20:08
  • Quote: "I wish to find all users who do NOT have the specific role (private_account), and I wish to list them as (user_id, actual_role_id)" - is this not what you wanted? – filiprem Aug 23 at 20:18
  • I should rephrase what I want with actual_role_id - I mean with that the new "to be inserted" role id, not the "existing role id". – paul23 Aug 23 at 20:31
  • then you need to rewrite the query ... remove the 1st join to "role", modify the SELECT list and add DISTINCT so you don't insert same row twice. – filiprem Aug 26 at 12:03
0

You can simply do another subquery:

SELECT 
    u.id, 
    (select r2.id from public."role" as r2 where r2."roleDescription"='private_account') as missing_role_id 
FROM public."user" AS u
WHERE u.id NOT IN (
    SELECT ur.user
    FROM public."userroles" as ur
    INNER JOIN public."role" as r
    ON ur.role=r.id
    WHERE r."roleDescription"='private_account'
)

If you object to 'private_account' appearing twice in the query, you an put it behind a CTE.

WITH j as (select id from public."role" where "roleDescription"='private_account')
SELECT 
    u.id, 
    (select id from j) as missing_role_id 
FROM public."user" AS u
WHERE u.id NOT IN (
    SELECT ur.user
    FROM public."userroles" as ur
    WHERE ur.role=(select id from j)
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.