CODEWITHSUNDEEP
mysqlsql
Artemkller545
Artemkller545

Reputation: 999

Selecting if all row ids exists in other table with column check

I have two tables, sites and callback_votes where sites holds the available sites to vote on, and callback_votes holds the votes that has been done on each site.

callback_votes is structured like this:

img
(source: gyazo.com)

Each time someone votes, the callback adds a new callback_votes row with the site id he voted on into site_id and the username, ip address he voted from.

Now what I want to do is, check if user has voted on all sites in the table sites by going through all callback_votes rows for the given ip, and check if it's site_id matches in the sites table.

Here's what I tried to do:

SELECT sites.* 
FROM sites 
WHERE (
    SELECT COUNT(*) 
    FROM callback_votes 
    WHERE callback_votes.ip = '127.0.0.1') = (
        SELECT COUNT(*) 
        FROM sites);

But this query won't work how I want, it only checks if there are the same count of votes with that ip as the number of rows on the sites table, which is cheat-able.

How can I do it the way I described?

EDIT;

My websites provides the sites to vote on, each site has its own id in the sites table. To vote on a site, the user clicks on the site (I load all site buttons using the sites table) and it sends him to the site's page. Once the user has voted, the site he voted on sends a callback to my site, and my site will create a new row in callback_votes with the site id he voted on, his ip and username.

Now I want to check if user has voted on all available sites by checking if the count of the votes in callback_votes is same, and the site_id matches to each other to prevent cheating.

Upvotes: 0

Views: 141

Answers (4)

krishnaaditya
krishnaaditya

Reputation: 860

Check the count is 0 from

select Count(*) from Sites
where Site_id NOT IN
(select Site_id from Votes where votes.user_ip = '127.0.0.1')

You can get the list of non voted sites too with the same cost by using

select Site_id from Sites
where Site_id NOT IN
(select Site_id from Votes where votes.user_ip = '127.0.0.1')

Upvotes: 1

Jaaz Cole
Jaaz Cole

Reputation: 3180

SELECT IP, USERNAME, CASE COUNT(DISTINCT SITE_ID) WHEN (SELECT COUNT(DISTINCT ID) FROM SITES) THEN 'VOTED FOR ALL' ELSE 'OTHER' END
FROM callback_votes
GROUP BY IP, USERNAME

Upvotes: 0

user3059697
user3059697

Reputation: 11

SELECT IF(COUNT(DISTINCT(c.site_id)) = (SELECT COUNT(s.site_id) FROM sites s), 'true', 'false') as voted_all
FROM callback_votes c
WHERE c.ip='127.0.0.1';

This returns true if they voted on every site and false if they didn't.

Upvotes: 0

Nawed Khan
Nawed Khan

Reputation: 4401

Get the number of sites that are not in distinct number of sites in callback where ip is user's ip:

Select count(*) From sites 
Where site_id NOT IN (Select DISTINCT site_id From callback_votes Where ip='1.0.0.127')

This will return count of sites not yet voted by this ip. If count is 0, then user has voted all.

Upvotes: 2

Related Questions