using where in with AND

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

using where in with AND

Post 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?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Do you have the right number of sixes? Is the ratings.user_id and integer or char field?
(#10850)
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post 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...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You may need to use a HAVING clause.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

ORDER BY user_rated_datetime AND thing_insert_datetime
maybe?
Post Reply