Mysql - SUM and INNER JOIN

[FiRe]

Can anyone help with this query? Here is what I am trying to do:

mysql_query("SELECT code.* FROM code INNER JOIN ratings ON code.id = ratings.codeid WHERE SUM(ratings.rating) >= 50 ORDER BY code.id DESC");

Obviously this won't work because you cant use SUM in the WHERE clause, but you can see what I am trying to do. Any ideas?

[edited by: FiRe at 1:41 pm (utc) on Feb. 23, 2007]

[eelixduppy]

Try to change this real quick to see if anything helps:

SELECT [b]*[/b] FROM code....

I have a feeling that may be it, but also if you are receiving any errors that would really help.

Add the "or die" statement:

mysql_query('blahblahblah') or die(mysql_error());

[FiRe]

Thanks but it didn't work, still getting the same error...

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

[omoutop]

try this one... although i am not sure it will work

SELECT * ,SUM(b.rating) AS sum_rating FROM code a, ratings b WHERE a.id=b.codeid AND sum_rating >= 50 ORDER BY a.id DESC

[eelixduppy]

Did you add the "or die" statement as i have it above? The error you are receiving can be due to three problems:
1) You did not connect to the database server correctly
2) You did not choose a correct database
3) You have an error in your query

By adding the "or die" statement that I have given you, it should let you know which of these it is, and if it's the third one, where to start looking :)

[FiRe]

eelixduppy I have been coding php/mysql apps for 3 years, I know what the error is telling me - it has nothing to do with connection issues!

Thanks omoutop but it didn't work, the main problem is because I am not selecting the ratings table until further in the statement and you cant use SUM before FROM (I think) which is why these variations are not going to work...

[eelixduppy]

eelixduppy I have been coding php/mysql apps for 3 years

I wasn't being rude. If you are experiencing an error in your query and you don't know how to fix it, the error from mysql will provide some insight, that's all. If you'd rather screw around with the query blindly, then go ahead, I'm just giving a suggestion. I did not intend to offend you.

[FiRe]

Sorry to sound rude I meant to say I know the error is "Invalid use of group function".

[edited by: FiRe at 4:00 pm (utc) on Feb. 23, 2007]

[syber]

You need to make it a correlated subquery

SELECT code.*  
 FROM code INNER JOIN ratings  
 ON code.id = ratings.codeid  
 WHERE (SELECT SUM(rating) 
   FROM ratings 
   WHERE codeid = code.id ) >= 50  
 ORDER BY code.id DESC