i need some help for a SQL query. Because I am new to this domain, i could not really explain google what i am trying to do, I didn't find any proper answer yet. Is it even possible with my tables?
I want to receive all the Threads (id, name), which are not my own Threads and which are not voted by me already. (i.e Threads i can vote)
The database looks like this:
Threads
+----+-------------+---------+
| id | thread_name | user_id |
+----+-------------+---------+
| 1 | Soccer | 1 |
| 2 | Running | 1 |
| 3 | Swimming | 2 |
+----+-------------+---------+
User
+----+--------+
| id | name |
+----+--------+
| 1 | Marcel |
| 2 | Marc |
| 3 | Susy |
+----+--------+
Votes
+----+-----------+---------+
| id | thread_id | user_id |
+----+-----------+---------+
| 1 | 1 | 3 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
+----+-----------+---------+
Example:
- If User 1 makes the query, he should receive the thread 3 because it is not his own and he did not voted yet.
- User 2 should receive thread 2
- User 4 should receive all the threads
I tried:
SELECT DISTINCT t.id, name FROM threads as t
LEFT JOIN votes as v ON v.thread_id = t.id
WHERE (v.user_id != USER_ID OR v.user_id IS NULL)
AND t.user_id != USER_ID
I am using Sequelize (Node.js ORM). pgAdmin gives me the following (simplified) as schema - hope this helps:
-- Table: "threads"
-- DROP TABLE "threads";
CREATE TABLE "threads"
(
id serial NOT NULL,
name character varying(255),
"createdAt" timestamp with time zone NOT NULL,
"updatedAt" timestamp with time zone NOT NULL,
"user_id" integer,
CONSTRAINT "threads_pkey" PRIMARY KEY (id),
CONSTRAINT "threads_user_id_fkey" FOREIGN KEY ("user_id")
REFERENCES "users" (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL
)
WITH (
OIDS=FALSE
);
ALTER TABLE "threads"
OWNER TO postgres;
-- Table: "votes"
-- DROP TABLE "votes";
CREATE TABLE "votes"
(
id serial NOT NULL,
"createdAt" timestamp with time zone NOT NULL,
"updatedAt" timestamp with time zone NOT NULL,
"user_id" integer,
"thread_id" integer,
CONSTRAINT "votes_pkey" PRIMARY KEY (id),
CONSTRAINT "votes_thread_id_fkey" FOREIGN KEY ("thread_id")
REFERENCES "threads" (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL,
CONSTRAINT "votes_user_id_fkey" FOREIGN KEY ("user_id")
REFERENCES "users" (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL
)
WITH (
OIDS=FALSE
);
ALTER TABLE "votes"
OWNER TO postgres;
-- Table: "users"
-- DROP TABLE "users";
CREATE TABLE "users"
(
id serial NOT NULL,
username character varying(255),
"createdAt" timestamp with time zone NOT NULL,
"updatedAt" timestamp with time zone NOT NULL,
CONSTRAINT "users_pkey" PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE "users"
OWNER TO postgres;
Thank you.