2

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.

4
  • MySQL or PostgreSQL ? Commented Jun 10, 2015 at 18:39
  • Ok, and what did you try ? Commented Jun 10, 2015 at 18:43
  • @MarcelSchulze can you also give schema of you tables i.e. \d <table name> ? Commented Jun 10, 2015 at 18:52
  • @shivams i am not sure, can you have a look at my update? thx Commented Jun 10, 2015 at 19:09

1 Answer 1

1

This should solve your problem.

select t.id from threads as t where t.user_id != USER_ID and t.id not in 
(select v.thread_id from votes as v where v.user_id = USER_ID);
Sign up to request clarification or add additional context in comments.

2 Comments

thank you. Looks like it is working this way. Never thought about using two select statements. Is it even possible with one?
@MarcelSchulze I cannot say for sure. I think for a little while to edit your query but was unable to do. So i rewrite it this way. I hope this will take you forward in your task which is what matter at the end ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.