Postgresql – Select/print data from subquery

postgresqlsubquery

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'
)

Best Answer

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.

Related Question