LEFT question

[CryoGen]

I'm sure this is easy, but I can't figure it. I keep getting an error on this saying that 'Initial' is an unknown column in the where clause.

  SELECT    system, acronym, id, year, cat, acronym, LEFT('system', 1) AS initial
  FROM      csap
  WHERE     year = '2004' AND initial = 'a'
  ORDER BY  system;

 

[sleipnir214]

To the best of my knowledge, you can't use a derived column in a WHERE clause. You can, however, use it in a HAVING clause.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[CryoGen]

Does the use of a HAVING clause require a GROUP BY clause as well? My DuBois book has one example and it uses that. How would it be structured?

 

[sleipnir214]

No, a HAVING clause does not require a GROUP BY clause.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[CryoGen]

Thanks, but I don't know how to structure the query using HAVING.

I need to be able to have the query search on the first letter in the system column as well as the date (the 'a' and the year are static, but will be dynamic in CF).

 

[sleipnir214]

According to

http://dev.mysql.com/doc/mysql/en/select.html

, it would be something like:

SELECT system, acronym, id, year, cat, acronym, LEFT('system', 1) AS initial
FROM csap
WHERE year = '2004'
HAVING initial = 'a'                                    
ORDER BY System;

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[CryoGen]

OK, that works with v5.0.2, but not with the version 4.10 that I'm using. Is there another way to get where I need to go without creating another column?

 

[sleipnir214]

I'm using MySQL 4.1.10, and the HAVING clause works as I described.

Are you getting an error or screwy behavior? I noticed that in my query, which was based on yours, the LEVT() invocation reads:

LEFT('system', 1)

This is incorrect, as the first parameter of the LEFT() function is the literal string "system", not the column name system. This is because of the single quotes.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[CryoGen]

You're right. I removed the quotes and the query ran fine. Thanks for the help.

 

[sleipnir214]

On my MySQL 4.1.10 installation, wht the table "foo" with the following data:

[tt]+------+--------+------+
| pkid | system | year |
+------+--------+------+
| 1 | aaaa | 2000 |
| 2 | bbbb | 2000 |
| 3 | cccc | 2000 |
| 4 | aaaa | 2001 |
| 5 | bbbb | 2002 |
| 6 | cccc | 2003 |
| 7 | dddd | 2004 |
| 8 | aaaa | 2004 |
+------+--------+------+[/tt]


The query:

select system, year, left (system, 1) as initial
from foo
where year=2004
having initial = 'a'

works and returns:

[tt]+--------+------+---------+
| system | year | initial |
+--------+------+---------+
| aaaa | 2004 | a |
+--------+------+---------+[/tt]


Want the best answers? Ask the best questions!

TANSTAAFL!!