Jump to content


Photo

HELP: Problem with MySQL column aliases


  • Please log in to reply
No replies to this topic

#1 theosib

theosib
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 10 March 2003 - 07:22 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users