I am using version 3.23.41 of MySQL, and I'm having a problem getting correct (expected?) behavior with column aliases.
I have a database A which has, among other things, an id field, something like this:
CREATE TABLE a {
somedata VARCHAR(10),
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
};I have a second table which records ratings of entries in the first:
CREATE TABLE b {
id INT UNSIGNED NOT NULL,
userid INT UNSIGNED NOT NULL,
plus TINYINT DEFAULT NULL,
minus TINYINT DEFAULT NULL,
PRIMARY KEY (id, userid)
};There are reasons why I want to track the positive and negative votes separately. Anyhow, here is a query that works and gives me the information I want:
SELECT somedata, a.id, COUNT(plus) AS pl, COUNT(minus) AS mi, COUNT(plus)-COUNT(minus) AS rating FROM a LEFT OUTER JOIN b ON (a.id=b.id) GROUP BY a.id ORDER BY rating;
This works fine, but it seems to me that having COUNT(plus) twice and COUNT(minus) twice may not be noticed and optimized by the database. My original query was this:
SELECT somedata, a.id, COUNT(plus) AS pl, COUNT(minus) AS mi, FROM a LEFT OUTER JOIN b ON (a.id=b.id) GROUP BY a.id ORDER BY (pl-mi);
From that, I get the following error:
ERROR 1054: Unknown column 'pl' in 'order clause'
Now, before, when I used to have only 'pl' and no 'mi', it worked great. I could use 'ORDER BY pl', and it would just work. Why can't I use '(pl-mi)'?
Next, I tried this query instead:
SELECT somedata, a.id, COUNT(plus) AS pl, COUNT(minus) AS mi, (pl-mi) AS rating FROM a LEFT OUTER JOIN b ON (a.id=b.id) GROUP BY a.id ORDER BY rating;
From that, I get this error:
ERROR 1054: Unknown column 'pl' in 'field list'
So my questions are:
- Is it going to be wasteful of time to have COUNT(plus) and COUNT(minus) twice? - Why can I use the alias in an ORDER BY clause by itself but not in an expression? - Why can't I use the alias in the SELECT after it's been created?
Thanks very much for your help
_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
