How to apply JOIN only if there are rows available

Question

Given the following database:

CREATE TABLE "Activity"
(
    "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    "designation" TEXT NOT NULL
);

CREATE TABLE "UserType"
(
    "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    "designation" TEXT NOT NULL
);

CREATE TABLE "User"
(
    "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    "name" TEXT NOT NULL,
    "typeId" INTEGER NOT NULL REFERENCES "UserType"("id")
    -- ... and other fields
);

CREATE TABLE "Activity_UserType"
(
    "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    "activityId" INTEGER NOT NULL REFERENCES "Activity"("id"),
    "userTypeId" INTEGER NOT NULL REFERENCES "UserType"("id")
);

How to write the following query:

Select users that have access to a given activity. There is a a caveat, if no rows are present/linked in Activity_UserType to this activity then it means all UserTypes are allowed to access the activity.

I tried the following query:

SELECT "User"."id", "User"."name", "User"."typeId"
FROM "User"
JOIN "UserType" ON "User"."typeId" = "UserType"."id"
JOIN "Activity_UserType" ON "UserType"."id" = "Activity_UserType"."userTypeId"
WHERE "Activity_UserType"."activityId" = 1;

However, this only works if the table "Activity_UserType" is not empty. I tried to apply a ´LEFT JOIN` but also without success.

Here is a dbfiddle with comments (expected outputs):
https://www.db-fiddle.com/f/nGHMWKw3ytYrCXePkzvizq/6

Answer 1

This is one simple solution.

Schema (PostgreSQL v15)

CREATE TABLE "Activity"
(
    "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    "designation" TEXT NOT NULL
);

CREATE TABLE "UserType"
(
    "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    "designation" TEXT NOT NULL
);

CREATE TABLE "User"
(
    "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    "name" TEXT NOT NULL,
    "typeId" INTEGER NOT NULL REFERENCES "UserType"("id")
    -- ... and other fields
);

CREATE TABLE "Activity_UserType"
(
    "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    "activityId" INTEGER NOT NULL REFERENCES "Activity"("id"),
    "userTypeId" INTEGER NOT NULL REFERENCES "UserType"("id")
);

INSERT INTO "UserType"(designation) VALUES ('UserType 1');
INSERT INTO "UserType"(designation) VALUES ('UserType 2');
INSERT INTO "UserType"(designation) VALUES ('UserType 3');

INSERT INTO "Activity"(designation) VALUES ('Activity All User Types');
INSERT INTO "Activity"(designation) VALUES ('Activity UserType 1');
INSERT INTO "Activity"(designation) VALUES ('Activity UserType 2');
INSERT INTO "Activity"(designation) VALUES ('Activity UserType 2 and 3');

INSERT INTO "Activity_UserType"("activityId", "userTypeId") VALUES (2, 1);
INSERT INTO "Activity_UserType"("activityId", "userTypeId") VALUES (3, 2);

INSERT INTO "Activity_UserType"("activityId", "userTypeId") VALUES (4, 2);
INSERT INTO "Activity_UserType"("activityId", "userTypeId") VALUES (4, 3);

INSERT INTO "User"(name, "typeId") VALUES ('User - Type 1', 1);
INSERT INTO "User"(name, "typeId") VALUES ('User - Type 2', 2);
INSERT INTO "User"(name, "typeId") VALUES ('User - Type 3', 3);

---

Query #1 -- Should return all users ('User - Type 1' and 'User - Type 2' and 'User - Type 3')

SELECT "User"."id"
     , "User"."name"
     , "User"."typeId"
FROM "User"
    JOIN "UserType"
        ON "User"."typeId" = "UserType"."id"
WHERE NOT EXISTS
(
    SELECT 1
    FROM "Activity_UserType"
    WHERE "Activity_UserType"."activityId" = 1
)
      OR EXISTS
(
    SELECT 1
    FROM "Activity_UserType"
    WHERE "UserType"."id" = "Activity_UserType"."userTypeId"
          AND "Activity_UserType"."activityId" = 1
);

id	name	typeId
1	User - Type 1	1
2	User - Type 2	2
3	User - Type 3	3

---

Query #2 -- Should return users Type1 ('User - Type 1')

SELECT "User"."id"
     , "User"."name"
     , "User"."typeId"
FROM "User"
    JOIN "UserType"
        ON "User"."typeId" = "UserType"."id"
WHERE NOT EXISTS
(
    SELECT 1
    FROM "Activity_UserType"
    WHERE "Activity_UserType"."activityId" = 2
)
      OR EXISTS
(
    SELECT 1
    FROM "Activity_UserType"
    WHERE "UserType"."id" = "Activity_UserType"."userTypeId"
          AND "Activity_UserType"."activityId" = 2
);

id	name	typeId
1	User - Type 1	1

---

Query #3 -- Should return users Type2 ('User - Type 2')

SELECT "User"."id"
     , "User"."name"
     , "User"."typeId"
FROM "User"
    JOIN "UserType"
        ON "User"."typeId" = "UserType"."id"
WHERE NOT EXISTS
(
    SELECT 1
    FROM "Activity_UserType"
    WHERE "Activity_UserType"."activityId" = 3
)
      OR EXISTS
(
    SELECT 1
    FROM "Activity_UserType"
    WHERE "UserType"."id" = "Activity_UserType"."userTypeId"
          AND "Activity_UserType"."activityId" = 3
);

id	name	typeId
2	User - Type 2	2

---

Query #4 -- Should return users Type2 and Type3 ('User - Type 2' and 'User - Type 3')

SELECT "User"."id"
     , "User"."name"
     , "User"."typeId"
FROM "User"
    JOIN "UserType"
        ON "User"."typeId" = "UserType"."id"
WHERE NOT EXISTS
(
    SELECT 1
    FROM "Activity_UserType"
    WHERE "Activity_UserType"."activityId" = 4
)
      OR EXISTS
(
    SELECT 1
    FROM "Activity_UserType"
    WHERE "UserType"."id" = "Activity_UserType"."userTypeId"
          AND "Activity_UserType"."activityId" = 4
);

id	name	typeId
2	User - Type 2	2
3	User - Type 3	3

---

View on DB Fiddle

Answer 2

Basically, return all users if no types are entitled explicitly. Check that in a CTE, and run a UNION ALL query depending on it:

WITH explicit AS (
   SELECT "userTypeId" AS "typeId"
   FROM   "Activity_UserType"
   WHERE  "activityId" = 1
   )
SELECT id, name, "typeId"
FROM   explicit
JOIN   "User" USING ("typeId")

UNION ALL
SELECT id, name, "typeId"
FROM   "User"
WHERE  NOT EXISTS (TABLE explicit);

fiddle

I dropped the join to "UserType". Referential integrity is enforced with FK constraints, and "User"."typeId" is NOT NULL, so the join would be noise in this query.

The query depends on user types being entitled, not users. There might be no users of an entitled type!

Relational design

Strictly speaking, you also need a UNIQUE constraint on table "Activity_UserType", or there can be duplicates - which we would have to fold:

... CONSTRAINT activity_usertype_uni UNIQUE ("activityId", "userTypeId")

Or just make it the PK, like:

CREATE TABLE "Activity_UserType" (
  "activityId" int NOT NULL REFERENCES "Activity"(id)
, "userTypeId" int NOT NULL REFERENCES "UserType"(id)
, PRIMARY KEY ("activityId", "userTypeId")              -- !
);

Aside

Your life with Postgres will be easier with all legal, lower-case identifiers, that never require double-quotes. See:

• Are PostgreSQL column names case-sensitive?