SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    Ireland
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySql selection command - giving wrong result

    This one has really got me! In summary, my problem is as follows:

    Example Table (name=vote_tbl). The table works by any user nominating a candidate by casting a first vote for them. Then other users can select a candidate from the list of candidates added to the vote_tbl and cast further votes on those. There is no separate candidate_tbl.

    Structure:

    |id|candidateid|voterid|vote|

    Data

    |1|10|11|no|

    |2|12|11|no|

    |3|19|11|no|

    |4|19|10|no|

    |5|20|10|no|

    Assuming that I am voterid 11, I want a select command that will tell me the candidateid's of the next candidate that I have not voted on.

    If I use a SELECT candidateid from vote_tbl where voterid !='11' LIMIT 1, then I will get results as follows:

    19

    Inclusion of candidate 19 is incorrect because I already voted on it. This happens because the above Select command picks up the 3rd vote. SELECT DISTINCT doesn't help here.

    Can anyone tell me how to amend the SELECT command so that the right answer of candidate 20 is presented.

    Many thanks,

    Will

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your logic in your statement is incorrect. You are asking for any row that doesn't include voter 11 and limit the results to one row. that would include all rows voted on by anyone other than voter 11.

    If you have mysql version 4.1 or higher you can use subqueries, try this for what you want:

    Code:
     select min(candidateid) from vote_tble
     where candidateid not in (
     select candidateid from vote_tble
     where voterid=11)
     )

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    Ireland
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dave.

    This looks like it could work and I will implement and let you know. Can I ask you though why you suggest to use 'min' on min(candidateid)? I presume the command would work equally well without it - can you confirm?

    Cheers,

    Will

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you implement an sql table there is no guarantee what order the records are stored in or retrieved in. I am guessing if there are candidates numbered
    7,8,12,13,14,17,20 in the database and you have voted on all but numbers 13 and 20 you would want to know the lowest number of candidate you haven't voted on and then proceed from there voting on each one. If you don't use min(candidateid) then you might get returned #20 and then before voting again someone nominates #21, 22, 23. There is a possibility that you won't realize you haven't voted for candidate #13 and could miss them alltogether. with the min in there it will return the lowest ranked candidate that has not been voted on by a user (in this case #11).

    Hope that clarifies.

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    Ireland
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dave.

    Would ORDER BY candidateid ASC LIMIT 10 achieve the same thing? - ignoring the 10 limitation of course.

    Last question - is the subquery a resource drain on a large database?

    Thanks,

    Will


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •