CODEWITHSUNDEEP
mysqlsqlselect
Ismail
Ismail

Reputation: 253

Select results where value of column is not equal to value of other column from other table

I'm trying to make a voting script, where people can rate teachers, but I have a problem.

I want the script to only show teachers that you haven't rated before, because it doesn't make sence to rate the same thing again and again.

I have 2 tables for this: teacher and rating

From the "teacher" table, i need the values of teacherId, teacherName and teacherImage

From the "rating" table, i want to compare the value of userId with the id of the logged in user (I have a variable with the value of the user)

But for testing, i just use the id "2".

When that is done, i want it to show every teacher, that this user haven't voted on yet. I have tried a lot of ways, but none of them worked as I wanted to.

My latest query is:

SELECT t.teacherId, t.teacherName, t.teacherImage, r.userId, r.teacherId
FROM teacher t, rating r
WHERE r.userId = 2 AND t.teacherId != r.teacherId

If anyone has an idea on how I could get this to work, I would appreciate it.

Thanks in advance.

Upvotes: 1

Views: 1754

Answers (1)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

There won't be any rating records for those teachers that has not been rated by a particular user. You may retrieve the list of teachers that has been rated by the user then use NOT IN. 

SELECT teacherId, 
       teacherName, 
       teacherImage
  FROM teacher 
 WHERE teacherId NOT IN (SELECT teacherId
                           FROM rating
                          WHERE userId = 2)

Upvotes: 1

Related Questions