Perplexing mySQL query [SOLVED]

[jmaccs64]

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


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



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!

[btherl]

I think you're looking for a left outer join.  Do you want rows from table A which are NOT in table B?

[code]$query="SELECT users.username FROM users LEFT OUTER JOIN surpool06 ON users.username=surpool06.surpool_username WHERE surpool06.surpool_username IS NULL";[/code]

The left outer join will give you nulls for table B where no matching rows exists.  Then your "is null" will pick up only those rows.

[fenway]

OUTER is implied; but where is this other table?

[jmaccs64]

Now I am just confused!!

[fenway]

What's surpool06?

[jmaccs64]

surpool06 in actually the table "votes" Sorry.....

[jmaccs64]

This is essentially what I want...but i know this doesn't work....Can someone explain to me why?

[code]$query="SELECT users.username FROM users
LEFT OUTER JOIN surpool06 ON users.username=surpool06.surpool_username
WHERE surpool06.surpool_week = $week AND surpool06.surpool_username IS NULL";[/code]

Thanks Again!

[shoz]

When explaining what you want from a query it is usually better to give examples of the tables (which you've done) [b]in addition to the example output desired[/b]. With an explanation of why that output is desired.

From what you've said I assume the following query is what you want. If it's not, then do an entire post with the examples.

Requires MYSQL >= 4.1
[code]
SELECT
u.username
FROM
users AS u
LEFT JOIN
(
    SELECT
    v.username, COUNT(*) AS sum_votes
    FROM
    votes AS v
    WHERE
    week = $week
    GROUP BY
    username
) AS vcount
ON
u.username = vcount.username
AND
u.vote_count = vcount.sum_votes
WHERE
vcount.username IS NULL
[/code]

The query should show any user who has not yet voted the number of votes alloted to them. Including users who may have voted more times than allowed.

[jmaccs64]

Essentially what i have to do is....

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.

I think the post you provided will do it, i am going to try.... THANKS

[shoz]

I should mention that if you're using this as a test to see whether someone is allowed to vote, then you should do one or both of these things

1) Change the query to the following
[code]
SELECT
u.username
FROM
users AS u
LEFT JOIN
(
    SELECT
    v.username, COUNT(*) AS sum_votes
    FROM
    votes AS v
    WHERE
    week = $week
    GROUP BY
    username
) AS vcount
ON
u.username = vcount.username
WHERE
vcount.username IS NULL
OR
u.vote_count < vcount.sum_votes
[/code]
This query should ensure that a user who has gone over his count doesn't continue to vote.

2) Get a WRITE lock on the voting table before doing the SELECT and release it after doing the INSERT to avoid a [url=http://en.wikipedia.org/wiki/Race_condition]Race Condition[/url]
[code]
LOCK TABLES vote WRITE
[/code]

EDIT: The WHERE condition had an "AND" where there should have been an "OR"

[jmaccs64]

This is the error it returns....


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY surpool_username ) AS vcount ON u.username = vcount.surpool_usernam' at line 13

Thanks for all your help!

This is the code:
[code]$query = "SELECT
u.username
FROM
users AS u
LEFT JOIN
(
    SELECT
    v.surpool_username, COUNT(*) AS sum_votes
    FROM
    surpool06 AS v
    WHERE
    surpool_week = $week
    GROUP BY
    surpool_username
) AS vcount
ON
u.username = vcount.surpool_username
WHERE
vcount.surpool_username IS NULL
OR
u.vote_count > vcount.sum_votes";
[/code]

[jmaccs64]

I GOT IT to Work...... :)

Now someone has to explain it to me....

[fenway]

If you can understand what the derived table produces, the entire query should make sense.