Sample Header Ad - 728x90

How to apply JOIN only if there are rows available

0 votes
2 answers
220 views
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
Asked by Exprove (9 rep)
Nov 7, 2023, 10:26 PM
Last activity: Nov 14, 2023, 05:25 PM