MYSQL :: Create A Dynamic Alias Name Using A Function

Dynamic Alias
I'm trying to output the previous 12 months and have the month name as the column heading. It doesnt want to let me use a function as the alias.

CODESELECT Department,
SUM(case when Month=MONTH((CURDATE() - INTERVAL 11 MONTH)) then Total else '0' end) AS DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%M-%Y'),

How Can I Create Dynamic Tables?
What I'm tryint to do is something similar to phpmyadmin, about creating tables in a database.

I've done the graphic interface, I have a form in which the user enters the name of the table as well as the number of fiels needed. Depending of this, then is displayed a text box and a list with the data types that exist.

The problem is I don't know how to do the php instruction with the array of the variables

UNION ALL Create Dynamic Columns
I have this query:

SELECT aff_code, aff_name, pw FROM affiliates WHERE aff_code = 'snow'
UNION ALL
SELECT aff_code, aff_name, pw FROM associates WHERE aff_code = 'snow'

But I want to know which table each row came from. Or at least be able to identifiy which row each came from. Any ideas on how to do this dynamically?

Create Dynamic Organization/pyramid Chart
Can anyone guide me how to create dynamic organizationI found similar sample.

the sample format is from right to left, furthermore it has no line showing so the chart not complete.

Can I Check The Value Of An Alias(i Think Its Called An Alias) In A Query.
I was wondering can I check the value of an alias(i think its called an alias) in a query.

At the moment I have this but its not working:

MySQL Code:

SELECT * , ( DAYOFYEAR( ToDate ) - DAYOFYEAR( FromDate ) ) AS NumDays
FROM `tbl_courses`
WHERE NumDays < 3 AND NumDays > 2 AND FromDate >= &#55614;&#57159;-10-17'
ORDER BY FromDate

How To Create A Function?
i'd like to create a function but i have a problem:

This is the script:

use DB
delimiter $
create function date_flow(isaero char(1), maturity date, freq int(1)) returns varchar(50)
begin
if freq='1' then
select datediff(curdate(),adddadte(prev_date,interval 1 year)) as b;
return b;
end if
end $
delimiter ;

when launching there is an error:
ERROR 1415 (0A000) at line 3: Not allowed to return a result set from a function

i know the problem is the select line but how to solve it?

Create Function
How can I create a function in mysql command prompt because when i try it throw a lot of errors but in mysql administrator they create find and they work find.

CREATE FUNCTION
Sorry for this n00b question but I can't find any material on this matter

As far as I can see I can create my own functions with the Command CREATE FUNCTION

I can see great use of this but to my best knowledge I can't just use this in query. I have to create a script yes?

If I create a function how do I call it in this script?

Create Aggregate Function
i want to create my own aggregate function........

when i run the following code :

[code]DELIMITER $$

DROP FUNCTION IF EXISTS `tradedb`.`abc` $$
CREATE AGGREGATE FUNCTION `tradedb`.`abc` () RETURNS DECIMAL
BEGIN

END $$

DELIMITER ;
[code]

i get the following error in the mysql query browser.

Script line: 4AGGREGATE is not supported for stored functions

Replication And Create Function
I'm running 5.0.4-beta with replication, and it works OK. Except...
When I use "create function" in the master, it does not get propagated
to the slave. Is this reasonable behavior? I don't think so. I'm
supposing it is a bug.

Dynamic LIKE And Dynamic ORDER BY (was "need Help With Mysql Query")
there are two tables, download, and download_cat
Im using one query to select something from two tables,
PHP Code:

select download.*, download_cat.* from download left join donwload_cat on download_cat.autoid='$_GET[cat]'
and download.cat=download_cat.autoid where donwload.name like '%$_GET[key]%'
order by download.'$_GET[order]' $_GET[desc]

IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere!

The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over Â£300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....

Alias Name Not Known
The bug I get is "Unknown column 'debt' in 'where clause'" . Why?

SELECT * , ventas.importe_acordado - SUM(cobros.nombre) , ventas.importe_acordado as nombre_venta
FROM ventas
INNER JOIN cobros
ON cobros.venta = ventas.id
WHERE debt > 0
GROUP BY ventas.id

Alias Error
error: Not unique table/alias: 'u'

sql: DELETE u.*,i.*,p.* FROM onig_users u, onig_users_info i, onig_users_profile p WHERE u.users_id=i.users_id AND i.users_id=p.users_id AND u.users_id = 23

i use this same syntax for a different design and had two tables instead of three and it works so I'm confused. Is there something different I should be doing for three tables?

Database Alias
Is it possible to create a database alias? I would like to have something like a symlink to another database. Let's say we have database "gurg" and this one should also be accessible through the name "garg". Is there a way to achieve this?

SELECT Alias
Is it possible to use an alias in the expression for another alias, for example:

SELECT net,
tax_rate,
net / tax_rate AS tax,
net + tax AS gross
FROM items

But that dosent work, mysql dosent allow an alias in the SELECT statement (says it cant find a field called tax), which is kind of annoying as it means i have to duplicate all my calculations like this:

SELECT net,
tax_rate,
net / tax_rate AS tax,
net + (net / tax_rate) AS gross
FROM items

This might not seem like a big deal in this query, but i have some other far more complicated queries where its just a pain. Is there any way to do this, it seems like it should be possible.

Sum Alias Column
I have a database mysql 5.0.51a-community with bookings.
I want to know how many days or weeks has been rented last year compared to this year.
The bookings I insert with arrival and departure date in 2 date type columns.
I do not know how to do it, they way I see it, I must count the days in every booking then sum the days.This seems a good way to me, but gives me error #1054 - Unknown column 'dias' in 'field list'
SELECT arrival, departure TO_DAYS(departure) - TO_DAYS(arrival) as days, typebooking, SUM(days) FROM bookings where typebooking = 'client' and year(arrival) = '2007'

As I understand the error, I can´t sum an alias column,...I could maybe do it with php, but don´t want to do a page for it, and must be a way to do it just with mysql.

Select Against Alias
Code:
SELECT COUNT(things) AS totalthings WHERE totalthings!=0;

> ERROR 1054 (42S22): Unknown column 'totalthings' in 'where clause'

How can I query against an alias, like above? It's late so maybe I'm missing something obvious..

Alias Problem
I get this error Unknown column 'n.nid' in 'on clause' when I run this query.Code:

SELECT DISTINCT(n.nid), e.event_start FROM event e
INNER JOIN node_access na ON na.nid = n.nid
INNER JOIN node n ON n.nid = e.nid
WHERE (na.grant_view = 1 AND
CONCAT(na.realm, na.gid) IN ('all0','simple_access0'))
AND n.status = 1 AND (
(e.event_start > 1138766400 AND e.event_start < 1141185599)
OR (e.event_end > 1138766400 AND e.event_end < 1141185599)
OR (e.event_start < 1138766400 AND e.event_end > 1141185599))
ORDER BY event_start;
Since node n is declared I can't see what's wrong.

Trouble Ordering By An Alias
Probably something silly here... why am I unable to specify my custom ordering?

SELECT
CASE
WHEN temp.`time` < 11 THEN Ɔ-10 seconds'
WHEN temp.`time` >= 11 AND temp.`time` < 31 THEN ཇ-30 seconds'
WHEN temp.`time` >= 31 AND temp.`time` < 61 THEN ཛ-60 seconds'
WHEN temp.`time` >= 61 AND temp.`time` < 181 THEN Ƈ-3 minutes'
WHEN temp.`time` >= 181 AND temp.`time` < 601 THEN Ɖ-10 minutes'
WHEN temp.`time` >= 601 AND temp.`time` < 1801 THEN ཆ-30 minutes'
ELSE ཚ+ minutes' END AS `visitLength`,
COUNT(*) AS `count`
FROM (SELECT MAX(`time`) - MIN(`time`) AS `time` FROM test_log GROUP BY `visitorId`) temp
GROUP BY `visitLength`
ORDER BY FIELD(`visitLength`, Ɔ-10 seconds', ཇ-30 seconds', ཛ-60 seconds', Ƈ-3 minutes', Ɖ-10 minutes', ཆ-30 minutes', ཚ+ minutes');

ERROR 1054 (42S22): Unknown column 'visitLength' in 'order clause'
Without an ORDER BY, I get the rows I want, without the ordering of course:

+---------------+-------+
| visitLength | count |
+---------------+-------+
| 0-10 seconds | 3823 |
| 1-3 minutes | 796 |
| 10-30 minutes | 394 |
| 11-30 seconds | 430 |
| 3-10 minutes | 682 |
| 30+ minutes | 666 |
| 31-60 seconds | 236 |
+---------------+-------+
7 rows in set (0.23 sec)

Can A Query Alias Be Used As A URL Parameter?
Question: Is is possible to use an alias created in a MySQL query as a URL parameter? (I'm not sure if this is a MySQL issue, a PHP issue, both, or neither). My database table uses self-referencing ids to establish hierarchy. In the temporary example below flow is my PK, and both parent and top are self-foreign. .....

Using Column Alias Name In WHERE Clause
I am giving alias name to the column in SELECT clause and when I am using same alias name in WHERE cluase, it is giving error.

We can use alias names in WHERE clause.

Is there any solution or other way to achieve same functionality?

QUERY:
select concate(firstname, " ", lastname) as name
from user
where name = "Chetan Parekh";

Alias Using Unicode Character
how to use the micro sign in an alias? I want the column name to appear as "Chl (Âµg/L)".

Column Alias On Mass
Is possible to retrieve all columns and alias them all at once. Eg, normally
you would write

select * from products which would return

id | name | price
-----------------------
1 | Test | 14.00

but I want to be able to say

select p.* from products p, so that it returns the columns as such

p.id | p.name | p.price

Obviously I can do this manually as such

select p.id, p.name, p.price from products p ....

But that would take a lot of big queries as some of my tables have 50
columns.

Column Alias And HAVING Clause
I am having problems with the HAVING clause. I know it can use aliases
but the alias I am using has a space in it, eg 'Device ID'

My querry looks like (simplified, devID is a calculation)

SELECT devID AS 'Device ID' FROM tblDevice HAVING 'Device ID' = '123'

and it does not work

but if I use

SELECT devID AS 'Device_ID' FROM tblDevice HAVING Device_ID = '123'

everything works.

How can I use an alias that contains a space in the HAVING clause?

Every Derived Table Must Have Its Own Alias
i have a sub-query i'm trying to execute as follows....and i'm getting the above error message.....

select z.date from (select x.date from bsmtruckrecord as x where x.employee_ida=17) where z.date in (select y.holiday from bsmpublicholiday as y) as z

i have referenced all tables by an alias and i'm still getting the error message saying....
"every derived table must have its own alias"

Not Unique Table/alias
I've written SQL before and I'm sure this is close to correct, but I have never written SQL for MySQL. Where am I going wrong? I'm getting a Not unique table/alias: 'movies' error.

SELECT movies.ID, movies.Title, movies.GenreID, movies.RatingID, movies.UserID, Genres.ID, Genres.genre, Ratings.ID, Ratings.rating, Users.ID, Users.FirstName, Users.LastName, Users.Email, Users.Phone
FROM ((movies INNER JOIN Genre ON movies.GenreID = Genres.ID), movies ON Ratings.ID = movies.RatingID), movies ON movies.UserID = Users.ID

Getting Not Unique Table/alias
Can anyone tell me why im getting this error;

Not unique table/alias: 'q'

When running the following query;

DELETE q.*, r.* FROM questions AS q, replies AS r WHERE r.id_question = q.question_id AND q.question_id = 55

When were alias's first done be mysql as i've just discovered that my provider are running version 4.0.25.

Column Alias In WHERE Clause
I wana select 2 fields of a table as a variable and then in that query use that variable like this
SELECT money1+money2 AS money WHERE money > 10000
but mysql return an sysntax error Is there any way to use a varible like this in a query?

Union Result Going To Other Table Alias
I need to check two tables for a value of 'pending'. Now, this value might exist or might not. So i figured a union would do the trick.

MYSQL
SELECT cats AS alias_one FROM table1 WHERE FIELD = 'pending'UNIONSELECT dogs AS alias_two FROM table2 WHERE FIELD = 'pending'
but if table2 is the only table with the result, mysql returns the contents of col_two but under the alias: alias_one.. how can i get around this?

Not Unique Table/alias: 'tbl_products'
I have the following

SELECT tbl_products.product_id,
tbl_products.product_MerchantProductID,
tbl_prdtscndcats.scndctgry_ID,
tbl_prdtscndcats.scndctgry_Name,
tbl_prdtscndcat_rel.prdt_scnd_rel_product_id,
tbl_prdtscndcat_rel.prdt_scnd_rel_scndcat_id
FROM tbl_products, tbl_prdtscndcats, tbl_prdtscndcat_rel

LEFT OUTER
JOIN tbl_products
ON tbl_products.product_id = tbl_prdtscndcat_rel.prdt_scnd_rel_product_id
LEFT OUTER
JOIN tbl_prdtscndcats
ON tbl_prdtscndcats.scndctgry_id = tbl_prdtscndcat_rel.prdt_scnd_rel_product_id

It's saying Not unique table/alias: 'tbl_products'.

Is there anything wrong with the SQL?

Cannot Refer To Alias In Mysql Calculation
Allright, newest problem. I am trying to do most of my calculations in mysql rather than PHP since that makes using the data a lot easier. The problem is I can't seem to use aliases I've assigned to data in further operations.

This query:

SELECT
SUM(CASE WHEN correct = 'y' THEN 1 ELSE 0 END) as top,
SUM(CASE WHEN correct = 'n' THEN 1 WHEN correct = 'y'
THEN 1 ELSE 0 END) as bottom,
ROUND((top/bottom)*100,1) as percent
FROM quiz_answers
LEFT JOIN quiz ON quiz.ID = quiz_answers.quizid
LEFT JOIN cadets ON quiz_answers.cadet_id = cadets.cadet_id
WHERE flight='$row[flight]' AND due>='$start' AND due<='$end'
Does not give me the percent. I get this error.
Quote:

Unknown column 'top' in 'field list'

That is true, there is no column top I wanna use the sum i just assigned as top.

I don't want to put the SUMs in the round function because I may need to use that data too.

Derived Table Alias Error
[MySQL][ODBC 3.51 Driver][mysqld-5.0.24-community-nt]Every derived table must have its own alias

is the ERROR displayed and my QUERY is....

Use An Alias Name For A Field In A Join Ation
I'm using a select like this:

SELECT * FROM `CPESER` INNER JOIN `CONCPE` ON `CPESER`.`id_concpe` = `CONCPE`.`id`
LEFT JOIN `CADSER` ON `CPESER`.`id_cadser` = `CADSER`.`id` LEFT JOIN `CADPRO`
ON `CADSER`.`id_cadpro_data`= `CADPRO`.`id` AND
`CADSER`.`id_cadpro_voice` = `CADPRO`.`id` LEFT JOIN `CONASS` ON
`CONCPE`.`contract` = `CONASS`.`contract` INNER JOIN `CADCLI` ON
`CONASS`.`id_cadcli` = `CADCLI`.`id` ORDER BY `CADCLI`.`name`

but there is a problem: some colums has the same name... it's possilbe to use aliases to the fields?

Alias Mysql ERROR 1064
I am trying to configure the mysql install, following the instructions in the manual, specifically the alias to the mysql path and I get the following error:

mysql> alias mysql=/usr/local/mysql/bin/mysql/
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alias mysql=/usr/local/mysql/bin/mysql/' at line 1

SQL, Field Name Alias For Write Access
I get an error on the adorec.update line - Unknown column 'MyName' in 'field list'. The code will work for read access but won't let me write to it. How do I update a field with an alias?

I know i can lose the "AS" part but that would defeat the purpose of what I'm accually trying to do.

Strange Alias And Join Problem On Server But Not Dev Box
I'm running MySQL 5.0.45, php5 and apache 1.3 on windows xp for development. All my tables are MyIsam.
The following query works fine :

MySQL
SELECT
i.image_id, image_filename, region_name, monument_name
FROM
image AS i
LEFT JOIN
image2region USING(image_id)
LEFT JOIN
region USING(region_id)
LEFT JOIN image2monument AS i2m ON (i.image_id=i2m.image_id)
LEFT JOIN monument USING(monument_id)
WHERE region_name='egypt'
LIMIT 0, 8
On the server (MySql v5.0.15) where I plan to host the site I get the following error:

#1054 - Unknown column 'image_filename' in 'field list'

If I give all the tables aliases and prepend all the column names after the SELECT with the aliases it works but I don't want to have to do this with all the queries I've written. I don't understand why it doesn't work on my dev box but not the hosts server.

I have also noticed that this problem only seems to arise on queries with more than two joins.

Has anyone else experienced a problem like this?

Or should I be prepending all columns with aliases as a matter of good practise anyway?

Cheers x

Error 1066 Not Unique Table/alias
I want to select 2 names from Person table. I think a join table would be overboard for this simple requirement, let me know.

person (table)
- id_person
- name

place (table)
- id_place
- name
- id_person1 [fk person.id_person]
- id_person2 [fk person.id_person]

SELECT place.name AS place_name, person.name AS name1, person.name AS name2
FROM place
INNER JOIN person ON place.id_person1=person.id_person
INNER JOIN person ON place.id_person2=person.id_person

ERROR 1066 (42000): Not unique table/alias: 'person'

Wanting output as
place_name: "This Place"
name1: "Fname Lname"
name2: "Fname Lname"

Derived Tables / Alias Error When Doing A Union
I am running version 4.1.1a of MySQL server, and I am running into a problem. The below SQL query causes an error, saying:

[MySQL][ODBC 3.51 Driver][mysqld-4.1.1a-alpha-nt]Every derived table must have it's own alias

I have looked at this query up and down and can't find anything wrong with it. I did find somewhere that there is possibly a bug with MySQL and this issue? But according to what I read, it had already been fixed in this version? Is the probem with my code, or is it a bug in the MySQL version that I have? Code:

ERROR 1066: Not Unique Table/alias: 'person'
The database 'testdb' has 2 tables, 'person' and 'team', which have the same structure. I tried the query "LOCK TABLES team READ, person AS team WRITE;", and got the following error: ERROR 1066: Not unique table/alias: 'person' What is the worry?

Dynamic WHERE
I would like to my WHERE statements to be dynamic.

For example:

I would like to adapt my WHERE statements based on the variables where defined.

So if $car="Ferrari", I would like my my where statement to look like.... WHERE car='Ferrari'.

Let's say $car="Ferrari" and $model="360 Modena", I would like the where statement to look like....WHERE car='Ferrari' AND model="360 Modena"

PHP - Dynamic SQL
I am trying to build a search page using PHP, SQL, and MySQL.But no matter what I do I can't seem to get it to work.

The error I am getting on this code is: Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'n.dob ASC ORDER BY n.employment_type ASC' at line 54

The code is as follows: Code:

Dynamic SQL
Is there a way to process something immediatly? for example in Oracle I would say execute immdiate and select a column based on what was executed.

Dynamic Select Value
I am trying to figure out how to do a query. I have a field called status, which is either 1,2,3,4,5. Then I have various fields such as date_1, date_2, date_3, date_4, date_5.

I would like to do something like the following:

SELECT status WHERE status = 1 OR status = 2 OR status = 3 OR status = 4 OR status = 5;

Then from that status I need to pull the proper date, so if status = 1, then SELECT date_1. Or if status = 2, then SELECT date_2.

How is this possible without mutliple queries? I need to do this in one query.

Dynamic SQL In MySQL
Is it possible to create Dynamic SQL queries in MySQL, like in SQL Server? i.e., the EXECUTE command or sp_executesql stored procedure in SQL Server.

Dynamic Selectrequest
I wanna have a selectrequest in my code, so that the selectrequest changes one parameter, but my solution wonÂ´t work.

Inside the table "Names" are names i wonna selct, to select them there is "Number"

for ($i=0; $Dyni<10; $i++)
{
$sqlab = "select name from Names where Number = i";
}

//At this place the "i" isnÂ´t working. It is only worken if I take "i"fixed value

$sqlab = "select name from Names where Number = 3;

How can I bring in that kind of dynamic changing.

Dynamic Locks
After recently changing from MSSQL to MYSQL I have encountered a couple of problems.
Here is an sql statement used in a Dll which connects to MYSQL database. The query seems to fall down at the point of rs1.UPDATE does MYSQL not support this sort of structure, syntax?

I dont think it is to do with the dynamic locks as they are enabled and my database supports them (I think) although when running a test query it has struggled with adLockOptimistic but is fine with the other two.

rs1.open "select * from table where col_id='" & var & "' and col_1='" & var1 & "'", con, adOpenDynamic, adLockOptimistic
rs1!col_2 = stat
rs1.Update
rs1.Close

Create A Dynamic Alias Name Using A Function

Is this possible? SELECT sum(if(EXTRACT(YEAR_MONTH FROM hitdate) = EXTRACT(YEAR_MONTH FROM now()),1,0)) as EXTRACT(YEAR_MONTH FROM now()) FROM TABLE_NAME

Is this possible?

SELECT

sum(if(EXTRACT(YEAR_MONTH FROM hitdate) = EXTRACT(YEAR_MONTH FROM
now()),1,0)) as EXTRACT(YEAR_MONTH FROM now())

FROM TABLE_NAME