André Hänsel wrote:
Hi,
I have one table with images (image_id, name, filename, vote_count) and one
table with votes (vote_id, image_id, vote_value, user_id).
I want to find the image with the lowest vote_count that a known user has
not yet voted.
For MySQL 4.0 I have the following:
SELECT i.* FROM images i LEFT JOIN votes v ON i.image_id = v.image_id WHERE
v.id IS NULL OR v.user_id != '1234' GROUP BY image_id ORDER BY vote_count
ASC LIMIT 1;
Now my three questions:
Is this the optimal query?
That query should actually produce an error because the "group by
image_id" is ambiguous - it's in both tables and you're not qualifying it.
Anyway, do you have an index on i.image_id and v.image_id ? That will
make sure the join is reasonably fast.
Is there a better query when using MySQL 4.1?
Not really, mysql5 has subqueries but mysql4.1 doesn't.
Since it's redundant, can I get rid of the vote_count column?
Why is it redundant? You said you need the one with the lowest count
that the user hasn't voted on. (I could also be mis-reading your question).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
