CODEWITHSUNDEEP
sqlsqlitegroup-bydistinct
Sjaakey
Sjaakey

Reputation: 18

SQLite not getting double data

I have rating system which people can rate on ideas. The user can get a summary from the SQLite database of ideas he hasn't voted for yet. My problem is that the database still gets the ideas he already voted for. My Table where the query has to operate looks like this:

ID (pk), ratingScore, userID, ideaID  
1         3           1       1
2         4           2       1
3         5           2       2

Ratings score is the score the user has given the idea, userID is the ID of the user who voted and ideaID is the ID of the idea.

How can I get the ideas from user 1 where he hasn't voted for without getting the other ideas, so the result of the query on this table would have been only 'ideaID 2'. 

SELECT Ratings.ideaID
FROM Ratings
WHERE (NOT(Ratings.userID)=1)
GROUP BY ideaID;

This is what I tried, but it didn't work, I also tried DISTINCT but that doesn't work either.

Upvotes: 0

Views: 52

Answers (2)

CL.
CL.

Reputation: 180010

Assuming that you have an Ideas table, you could use a correlated subquery like this to find ideas for which no rating for that user exists:

SELECT ID
FROM Ideas
WHERE NOT EXISTS (SELECT 1
                  FROM Ratings
                  WHERE userID = 1
                    AND ideaID = Ideas.ID)

(Reading the ideas from the Ratings table would fail if there is an idea that has not been rated by any user.)

Upvotes: 1

cha
cha

Reputation: 10411

You need to get a list of all ideas and left join it to your table, like this:

SELECT ideas.ideaID
FROM (SELECT DISTINCT ideaID FROM Ratings) ideas LEFT JOIN 
Ratings r ON r.ideaID = ideas.ideaID AND r.userID=1 
WHERE r.ideaID IS NULL;

The example above will give you a list of unvoted ideas for a single user.

Upvotes: 1

Related Questions