Jump to content


Photo

Perplexing mySQL query [SOLVED]


  • Please log in to reply
12 replies to this topic

#1 jmaccs64

jmaccs64
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 13 September 2006 - 03:38 AM

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!

#2 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 13 September 2006 - 07:38 AM

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

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

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.

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 September 2006 - 03:16 PM

OUTER is implied; but where is this other table?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 jmaccs64

jmaccs64
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 14 September 2006 - 12:21 PM

Now I am just confused!!

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 September 2006 - 02:56 PM

What's surpool06?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 jmaccs64

jmaccs64
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 14 September 2006 - 10:02 PM

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

#7 jmaccs64

jmaccs64
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 14 September 2006 - 10:24 PM

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

$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";

Thanks Again!

#8 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 14 September 2006 - 10:47 PM

When explaining what you want from a query it is usually better to give examples of the tables (which you've done) in addition to the example output desired. 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
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

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.

#9 jmaccs64

jmaccs64
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 14 September 2006 - 10:51 PM

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


#10 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 14 September 2006 - 11:09 PM

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
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
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 Race Condition
LOCK TABLES vote WRITE

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

#11 jmaccs64

jmaccs64
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 15 September 2006 - 02:15 AM

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:
$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";


#12 jmaccs64

jmaccs64
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 15 September 2006 - 02:45 AM

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

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

#13 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 September 2006 - 12:36 PM

If you can understand what the derived table produces, the entire query should make sense.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users