Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    31

    Unanswered: Can't seem to get correct query...

    I can't seem to create the correct query. I have 2 tables and I need to return results from both. Here is the basic structure:

    Code:
    vbc_challenges
    challengeid	catid	votes	score
    Code:
    vbc_categories
    categoryid	cat	scale	entrants
    These are the relevant fields. I am trying to return the highest score, highest votes, scale, entrants and cat for each categoryid. categoryid and catid are associated with each other. Basically I thought this query would work:

    SELECT cat, scale, entrants, MAX(score) AS highest, SUM(score) AS total, AVG(score) AS average
    FROM vbc_categories, vbc_challenges
    WHERE categoryid = catid
    GROUP BY categoryid

    It doesnt work like I thought it would. Am I missing something?

  2. #2
    Join Date
    Dec 2003
    Posts
    31
    It actually does work correctly unless there is no entries. Is there any way to change that to work correctly if there are no entries?
    vBulletin Hacking God - Portfolio
    FireFoxForum.com - Where people talk about FireFox.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select cats.cat
         , cats.scale
         , cats.entrants
         , max(chgs.score) as highest
         , sum(chgs.score) as total
         , avg(chgs.score) as average
      from vbc_categories as cats
    left outer
      join vbc_challenges as chgs
        on cats.categoryid 
         = chgs.catid
    group 
        by cats.cat
         , cats.scale
         , cats.entrants
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2003
    Posts
    31
    Absolutely perfect!!! I really need to learn some of the main MySQL commands.

    Thanks so much!!!
    vBulletin Hacking God - Portfolio
    FireFoxForum.com - Where people talk about FireFox.

  5. #5
    Join Date
    Dec 2003
    Posts
    31
    Anyone else able to help me. A new problem. I have 2 other columns in the vbc_challenges table. Lets call them field1 and field2 that's basically how they're called. I need to count the number of unique entries in those columns. So if field1 has something in it and the same entry is in field2 then only count it once. Is there an easy way to do this?
    vBulletin Hacking God - Portfolio
    FireFoxForum.com - Where people talk about FireFox.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should really start a new thread when you have a new question

    there is no easy way to do this in mysql before version 4.1
    Code:
    select count(*)
      from (
           select field1 from vbc_challenges 
           union
           select field2 from vbc_challenges
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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