Jump to content

Archived

This topic is now archived and is closed to further replies.

jmaccs64

Perplexing mySQL query [SOLVED]

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
OUTER is implied; but where is this other table?

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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"

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
If you can understand what the derived table produces, the entire query should make sense.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.