SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict say's Avatar
    Join Date
    Sep 2003
    Location
    At work
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by 2nd table

    Hi,

    previously, I have a members table like this:

    PHP Code:
    +----+----------+----------+
    id username password |
    +----+----------+----------+
    1  |   mary   |  sdjft8  |
    +----+----------+----------+ 
    but now, I have added a new table for vote and I would like to get results order by the most vote. I haven't have any idea on how to do this so any ideas is appreciated.

    PHP Code:
    +----+--------+------+
    id userid vote |
    +----+--------+------+
    1  |    1   |   5  |
    +----+--------+------+ 

  2. #2
    SitePoint Enthusiast Redprince's Avatar
    Join Date
    May 2004
    Location
    Salzgitter, Lower Saxony, Germany
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not very difficult:
    Code:
    SELECT tbl1.username FROM table1(user + pw) AS tbl1, table2(vote) AS tbl2 WHERE tbl1.id = tbl2.userid ORDER BY tbl2.vote DESC
    Should work But change name and alias in there

  3. #3
    SitePoint Addict say's Avatar
    Join Date
    Sep 2003
    Location
    At work
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply,

    One user is allow to have more than one vote, wouldn't I need a COUNT(vote.id)??

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select vote
         , count(*) as votecount
      from votestable
    group
        by vote
    order
        by votecount desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict say's Avatar
    Join Date
    Sep 2003
    Location
    At work
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy, I'll have a try later

  6. #6
    SitePoint Addict say's Avatar
    Join Date
    Sep 2003
    Location
    At work
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi rudy, I have the following:

    PHP Code:
    $sql 'SELECT members.id, members.username, count(vote.id) as votecount 
    FROM members LEFT JOIN vote ON members.id = vote.userid group by vote.id order by votecount desc'

    This result output only username who have voted. Those users who have not voted before doesn't come out because there are no records in the vote table that contains their userid. But I would like my records to be:

    Mary - 5 votes
    Darren - 3 votes
    Terry - 0 vote

    Is it possible? Will ISNULL work for those who no record in the vote table but still retrieve their member info? Thanks in advanced

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "This result output only username who have voted" because you're grouping on the wrong column

    by the way, i just got home from voting

    (canada chooses a government today)

    Code:
    select members.id
         , members.username
         , count(vote.id) as votecount 
      from members 
    left outer
      join vote 
        on members.id 
         = vote.userid 
    group 
        by members.id
         , members.username
    order 
        by votecount desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict say's Avatar
    Join Date
    Sep 2003
    Location
    At work
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow rudy, I've nothing to say about you but you are truly an sql expert. You've help lots of people here and have gain my respect Thanks a million for your help.

    PS: I hope you didn't write out this query statement in your voting form. Lol. J/k


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
  •