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
Last activity: Nov 14, 2023, 05:25 PM