Page 1 of 1

using where in with AND

Posted: Fri Dec 07, 2007 5:10 pm
by GeXus
I have two tables:

Users
user_id

Ratings
user_id
by_user_id
rating

I'm trying to find which user_ids have not yet been rated by a specific user from a list of user_ids and return the rest... This is what I have below...




Code: Select all

SELECT users.user_id AS user_id, ratings.by_user_id as by_user_id
FROM users
LEFT JOIN ratings ON users.user_id = ratings.user_id
WHERE users.user_id
IN (
'503039', '1001359', '1506714', '1514587', '2237485', '2258441', '4704941', '5400415', '5708099', '5713117', '5713826', '8108791', '15200060', '15803712', '20800798', '25200094', '27000125', '31202619', '38004633', '73700472', '500298800', '500572691', '507151077', '508512579', '512696218', '557185747', '567142464', '582327844', '601599551', '653107128', '655493799', '657613062', '659913802', '669097874', '676297893', '736560493', '762208539', '822804126'
)
AND  ratings.user_id != '66666666'



When I add the 'AND ratings.user_id != '66666666'' I get nothing....

Any ideas what I'm doing wrong?

Posted: Fri Dec 07, 2007 7:09 pm
by Christopher
Do you have the right number of sixes? Is the ratings.user_id and integer or char field?

Posted: Fri Dec 07, 2007 7:26 pm
by GeXus
arborint wrote:Do you have the right number of sixes? Is the ratings.user_id and integer or char field?
It's an int field.. but the 6's are just dummy... and since there are no records with that, it should return everything (at least that's what I want) now if there were any 6666666 it would exclude them...

Posted: Sat Dec 08, 2007 7:44 am
by feyd
You may need to use a HAVING clause.

Posted: Sat Dec 08, 2007 2:24 pm
by GeXus
Hmm... Well I think i might be going about this the wrong way... Here is what I'm ultimately trying to accomplish...

It's similar to hot or not, where you have a list of items and you rate those items. The way it should query is once you rate an item, it goes to the next one, and puts the previously rated item as the furthest down the list.. so it will go through the whole list before it repeats...

Any ideas how I should go about that?

Posted: Sun Dec 09, 2007 11:19 am
by feyd

Code: Select all

ORDER BY user_rated_datetime AND thing_insert_datetime
maybe?