Data Sets

Table "users"

username | password | vote_count
joe | 343423 | 3
mike | jhgjjh | 1
phil | sdfg4 | 2

Table "votes"
username | team | week
joe | Bears | 1
mike | Colts | 1
joe | 49ers | 1
phil | Bears | 1

I need to match the users database with the votes database to compile who has not voted their assigned vote count in the desired week....

This is what I have so far...and it's not working

$query="SELECT users.username FROM users
LEFT JOIN surpool06 ON users.username=surpool06.surpool_username
WHERE surpool06.surpool_username IS NULL";

It is not working because I can't tell it what week to look for and also i need to know assigned votes versus actual votes....

Please Help!!

Thanks in advance!

    jmaccs64 wrote:
    $query="SELECT users.username FROM users
    LEFT JOIN surpool06 ON users.username=surpool06.surpool_username
    WHERE surpool06.surpool_username IS NULL";

    Well, it is not strange that this query is not working. You try to get all records where the surpool_username is some name AND where it is null. Try the following instead:

    $query="SELECT users.username FROM users
    LEFT JOIN surpool06 ON users.username=surpool06.surpool_username
    WHERE surpool06.surpool_week = 1";

    If the users.vote_count is how many votes they have you could try this. Note that I haven't tried it myself, but I think it should work.

    SELECT users.username
    FROM users
    LEFT JOIN votes ON  users.username = votes.username
    WHERE votes.week = 1
    HAVING COUNT(votes.*) < users.votes
    GROUP BY users.username, votes.week

      This is not working..... and I have no idea how to remedy the problem....

      Thanks

        Essentially what i have to do is....
        I need a query to return this....
        I think it would have to be structered something like this.

        How many times a username repeats itself in a given week in the table surpool06, if that value is less than users.votes then return username.

        Thanks Again!

          Write a Reply...