Vaia Vaia - 1 year ago 66
MySQL Question

Exclude a specific SQL column?

I'm trying to

random pages that haven't been liked or disliked by a certain user (user-id:
) yet.

My table structure:

: (id, title)
: (id, page_id, uid, status)

And this is how I tried to get a random page:

SELECT AS page_id,
SUM(CASE WHEN l.page_id = AND status = '1' THEN 1 ELSE 0 END) AS likes,
SUM(CASE WHEN l.page_id = AND status = '0' THEN 1 ELSE 0 END) AS dislikes

FROM OneNight_pages_likes l
LEFT JOIN OneNight_pages p on l.page_id =
WHERE l.uid != '1'
GROUP BY page_id
ORDER BY rand()

However this will still display pages that I already liked or disliked, because
WHERE l.uid != 1
doesn't affect to exclude a whole
(because there are other likers and dislikers for a particular page_id).

I guess I need to solve this with a subquery to receive a page id first or use some specific algorithm? I also thought of storing all liked and disliked pages as an array in a cookie or session, but I don't know if that's the right or efficient way to do it?

Answer Source

Is this what you want?

select p.*
from OneNight_pages p
where not exists (select 1
                  from OneNight_pages_likes l
                  where l.page_id = and l.uid = '1' 
order by rand()
limit 1;

This selects a random page where user "1" has no rows in the likes table.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download