SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Threaded View

  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select merge where id is the same?

    hi all

    this is a pretty long query
    what i am trying to do, is select the five people that have voted me most
    the problem is, there are two tables which contain votes
    one uservotelog table contains all normal votes
    one supervote table contains all supervotes

    the following query can select all those that have voted for me
    and order them according to most votes
    but, if someone has done supervotes and normal votes, they will appear twice
    is it possible to add them together inside sql without affecting performance,
    rather than having to resort to some php array's and sorting?

    <--mysql 4.1.7-->
    Code:
        SELECT user_ID, user_Nick, sum
       FROM (
       SELECT user_ID, user_Nick, SUM( uservotelog_VoteValue )  AS sum
       FROM urban_userlog
       INNER  JOIN urban_uservotelog ON user_ID = uservotelog_VoterUserID
       AND user_AccountStatus =  'a'
       AND uservotelog_Type <>  'S'
       AND uservotelog_VoteeUserID =  '1'
       GROUP  BY user_Nick
       UNION 
       SELECT user_ID, user_Nick, (COUNT( usersupervote_UserID )  *20
       ) AS sum
       FROM urban_usersupervote
       INNER  JOIN urban_userlog ON user_ID = usersupervote_UserID
       WHERE usersupervote_Gender =  'guys'
       AND usersupervote_Date >=  '2005-02-01'
       GROUP  BY user_ID
       ) AS mostvotes
       ORDER  BY sum DESC
    sample output
    user_ID--- user_Nick---- votes
    1 --------- fred --------- 12
    2 ---------bill ---------12
    3 ---------tom ---------11
    5 ---------jo ---------10
    8 ---------liz--------- 8
    9 ---------greg --------- 7
    14 ---------frank ---------7
    8 --------- liz ---------6
    3 --------- tom --------- 2

    where tom and liz show up twice, for example they both did supervotes and normal votes
    and their accumulated vote totals would be 13 and 14 respectively
    and should therefore be at the top of the table, and only show up once

    thanks
    dave
    Last edited by dtra; Feb 1, 2005 at 00:22.


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
  •