i use a sql query like this to get some results i need:
SELECT * FROM pictures p WHERE p.id NOT IN ( SELECT picture_id FROM guesses g WHERE g.user_id = XXX ) AND p.user_id != XXX ;
Relation is as follows: A user has many pictures and a picture belongs to one user. A user has many guesses and a guess belongs to one picture. The tricky part is that a user is only allowed one guess for the same picture.
XXX = $user_id
I guess that there is a way to rewrite this sub-select using a left join but i can't get it working.
Can anyone help?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `guesses` ADD INDEX `guesses_idx_user_id_xxx_picture_id` (`user_id`,`XXX`,`picture_id`); ALTER TABLE `pictures` ADD INDEX `pictures_idx_user_id` (`user_id`);
SELECT * FROM pictures p WHERE NOT EXISTS ( SELECT 1 FROM guesses g WHERE ( g.user_id = g.XXX ) AND ( p.id = g.picture_id ) ) AND p.user_id != p.XXX