Using Mysql Alias in WHERE

ridesign1 · 06-13-2009, 04:26 PM

I am trying to execute the following query but I get unknown column for rating_score for the WHERE clause.

Code:

SELECT SUM(ratings.rating_rating) AS rating_score, posts.ID, posts.post_title FROM posts LEFT JOIN ratings ON ratings.rating_postid = posts.ID WHERE rating_score >= 1 GROUP BY posts.ID ORDER BY posts.post_date DESC LIMIT 5

sdsinc · 06-13-2009, 06:31 PM

MySQL ?
Use the full expression instead of the alias.
In aggregate expression use the HAVING clause instead of WHERE.
Fields that are not aggregated should be included in group by statement.

Something like this perhaps:

Code:

SELECT SUM(ratings.rating_rating) AS rating_score, posts.ID, posts.post_title 
FROM posts LEFT JOIN ratings ON ratings.rating_postid = posts.ID 
HAVING SUM(ratings.rating_rating) >= 1 
GROUP BY posts.ID, posts.post_title
ORDER BY posts.post_date DESC 
LIMIT 5

06-13-2009, 04:26 PM	THREAD STARTER #1 (permalink)
ridesign1 NamePros Regular Join Date: Mar 2005 Posts: 867	Using Mysql Alias in WHERE I am trying to execute the following query but I get unknown column for rating_score for the WHERE clause. Code: SELECT SUM(ratings.rating_rating) AS rating_score, posts.ID, posts.post_title FROM posts LEFT JOIN ratings ON ratings.rating_postid = posts.ID WHERE rating_score >= 1 GROUP BY posts.ID ORDER BY posts.post_date DESC LIMIT 5 __________________ Press Release Submission : Business.pk For sale\| Link Directory \| Perfume shop

06-13-2009, 06:31 PM	#2 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,558	MySQL ? Use the full expression instead of the alias. In aggregate expression use the HAVING clause instead of WHERE. Fields that are not aggregated should be included in group by statement. Something like this perhaps: Code: SELECT SUM(ratings.rating_rating) AS rating_score, posts.ID, posts.post_title FROM posts LEFT JOIN ratings ON ratings.rating_postid = posts.ID HAVING SUM(ratings.rating_rating) >= 1 GROUP BY posts.ID, posts.post_title ORDER BY posts.post_date DESC LIMIT 5 __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Cpanel downgrade MYSQL 4.1.X to MySQL 4.0.xx	Webhosting-live.com	Web Hosting Discussion	3	09-01-2006 06:12 PM
Great Scripts for Sale With Resale Rights!	Zeeble	Scripts For Sale	20	01-04-2006 02:39 AM
Tutorial: How to Install Apache2 MySQL and PHP on Windows	deadserious	Webmaster Tutorials	35	09-21-2005 10:46 PM
Tutorial: Getting Started With MySQL (The Basics)	deadserious	Webmaster Tutorials	3	04-18-2004 02:17 PM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)