Can't seem to get correct query...

WhSox21 · #1 (**permalink**) 11-04-04, 14:34

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?

WhSox21 · #2 (**permalink**) 11-04-04, 14:37

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?

r937 · #3 (**permalink**) 11-04-04, 16:38

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

WhSox21 · #4 (**permalink**) 11-04-04, 16:47

Absolutely perfect!!! I really need to learn some of the main MySQL commands.

Thanks so much!!!

WhSox21 · #5 (**permalink**) 11-04-04, 19:24

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?

r937 · #6 (**permalink**) 11-04-04, 19:56

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
       )

Internet Services

Web Development

Digital Marketing

Consumer Tech