CODEWITHSUNDEEP
postgresqljoinnull
Mantoze
Mantoze

Reputation: 75

Postgresql join with null value

I have a difficulty with query. My tables and data:

CREATE SEQUENCE a_files_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1;

CREATE TABLE "public"."a_files" (
    "id" integer DEFAULT nextval('a_files_id_seq') NOT NULL,
    "filename" character varying NOT NULL,
    "category_id" integer NOT NULL,
    "available_id" integer
) WITH (oids = false);

INSERT INTO "a_files" ("id", "filename", "category_id", "available_id") VALUES
(1, 'aa.jpg',   1,  NULL),
(2, 'bb.jpg',   1,  1);

CREATE SEQUENCE files_log_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1;

CREATE TABLE "public"."a_files_log" (
    "id" integer DEFAULT nextval('files_log_id_seq') NOT NULL,
    "user_id" integer NOT NULL,
    "file_id" integer NOT NULL,
    "created_at" timestamp NOT NULL
) WITH (oids = false);

INSERT INTO "a_files_log" ("id", "user_id", "file_id", "created_at") VALUES
(1, 100,    1,  '2021-03-14 17:04:34.068'),
(2, 100,    1,  '2021-03-14 17:04:46.176338'),
(3, 100,    2,  '2021-03-14 17:05:16.633936'),
(4, 100,    2,  '2021-03-14 17:05:30.279555');

CREATE SEQUENCE a_parameters_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1;

CREATE TABLE "public"."a_parameters" (
    "id" integer DEFAULT nextval('a_parameters_id_seq') NOT NULL,
    "door_id" integer NOT NULL,
    "category_id" integer NOT NULL,
    "available_id" integer NOT NULL
) WITH (oids = false);

INSERT INTO "a_parameters" ("id", "door_id", "category_id", "available_id") VALUES
(1, 1,  1,  1),
(2, 1,  1,  2),
(3, 1,  1,  3),
(4, 1,  2,  1),
(5, 1,  2,  2);

The visual tables:

enter image description here enter image description here enter image description here

My query:

SELECT f.filename as filename, fl.user_id AS user_id, count(fl.*) as count_views, max(fl.created_at) as last_view
FROM a_files_log fl
JOIN a_files f ON f.id = fl.file_id
JOIN a_parameters p ON 
   CASE WHEN f.available_id IS NOT NULL 
   THEN   p.category_id = f.category_id AND p.available_id = f.available_id
     ELSE   p.category_id = f.category_id
   END
WHERE p.door_id = 1
GROUP BY filename, user_id
ORDER BY count_views DESC

I get such records:

enter image description here

Expected result:

enter image description here

When available_id in a_files table is NULL, how to make a relationship that the result is like that I hope?

Upvotes: 1

Views: 1305

Answers (1)

eshirvana
eshirvana

Reputation: 24613

you need to get distinct count :

SELECT
    f.filename as filename,
    fl.user_id AS user_id,
    count(DISTINCT fl.*) as count_views,
    max(fl.created_at) as last_view
FROM
    a_files_log fl
JOIN a_files f 
    ON f.id = fl.file_id
JOIN a_parameters p 
    ON CASE WHEN f.available_id IS NOT NULL THEN p.category_id = f.category_id
    AND p.available_id = f.available_id ELSE p.category_id = f.category_id END
WHERE
    p.door_id = 1
GROUP BY
    filename,
    user_id
ORDER BY
    count_views DESC

Upvotes: 1

Related Questions