MYSQL :: Error 1066 Not Unique Table/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.

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?

Not Unique Table/alias: <tablename> For Temporary Table
Iâ€™m converting an application from Paradox to MySQL (first time Iâ€™ve used this) and am replacing Paradox local tables with MySQL temporary tables â€“ I understand each user will have a unique copy of the table. Basically I issue the following two commands :

'drop temporary table if exists <tablename>'

'create temporary table <tablename> like <tablename>'

where <tablename> is a table structure in the database with 0 records. This strategy was successful while I was working with a local copy of MySQL on my development PC, but when I run the system from a server on the office network I get the following error :

#42000Not unique table/alias: <tablename>

The environment I work in is Windows XP Pro SP2, Delphi 5 and CoreLabs MyDAC data access components. Iâ€™ve searched this site and Googled to no avail. Hoping someone has seen this before.

Error 1066
mysqldump: Got error: 1066: Not unique table/alias: 'cad' when doing LOCK TABLES

I cant find much info on this. What is wrong that makes this happen?

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....

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?

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

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:

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

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"

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?

Mysql Unique Key Error
i m trying to run it on my server where i hv hosted my site .. but it jus wont work it says error about how to use unique key ... please help me out here!!

CREATE TABLE `lyrics_albums` (
`id` int(11) NOT NULL auto_increment,
`artist` int(11) NOT NULL default '0',
`album` varchar(40) NOT NULL default '',
PRIMARY KEY (`id`)
UNIQUE KEY `album` (`album`)
) TYPE=MyISAM;

CREATE TABLE `lyrics_artist` (
`id` int(11) NOT NULL auto_increment,
`artist` varchar(30) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `artist` (`artist`)
) TYPE=MyISAM;

CREATE TABLE `lyrics_songs` (
`id` int(11) NOT NULL auto_increment,
`album` int(11) NOT NULL default '0',
`name` varchar(30) NOT NULL default '',
`text` text NOT NULL,
PRIMARY KEY (`id`)
UNIQUE KEY `name` (`name`)
) TYPE=MyISAM;

3,000,000 Unique INT(11) Low-Hi Ranges In My TABLE, Am I Stuck With FULL TABLE SCAN?
I have a table with 3,000,000 different IP netranges.

each row has a unique range: ie: 1xxxxxxx-1xxxxxxx2
..

I try to find the primary_index when querying WHERE var BETWEEN lowIP and highIPrange.

Since there all of the netranges are unique, it seems like my INDEXES are useless, so the only way to optimize this would be to horizontally partition the data? The query is extremely slow ~1 second in MyISAM, and ~.50 second in MEMORY table format.

I'm assuming the only option is to horizontally position the data to improve performance, am I correct?

Reference To Table Alias In From Clause To Be Used By Subquery InSelect Clause
I am using 5.0.26-NT on Windows 2000.

I have need to use a reference in the outer from clause in a subquery in
the select clause. Consider the following example:

Select (select b.baitID from b where b.entrydate curdate()) as
wantedBaitIDs from bait_tbl b;

My actual need is more complex than this as part of it is a rough cross
tab. If I try to define the table in the alias, not only do I lose
whatever benefits there are in the particular join I would use in the
outer from clause but would also require the join to be defined in each
subquery, requiring it to be examined each time it is used.

To be absolutely clear, in this example I want to use bait_tbl with the
alias of b in the subquery. In my actual query I reference the same
table twice with a different join set for each. I need to reference a
particular alias as that has the join set I need.

Update INT UNIQUE Column - Duplicate Entry Error
As an example, I'm creating a table which holds following values in MySql 5.0:

idcol lft (unique)
1 1
2 2
3 3
4 4

-> when I try to update the lft column using :

UPDATE unique_example
set lft = lft +1
where lft > 1;

I get following error message :

Duplicate entry '3' for key 2

Exactly the same approach works using SQL server 2000. Apparently, MySQL processes the rows one by one, as it throws an error on the UNIQUE lft column from whom I wish to update all rows at once? Code:

Finding Unique Ids From A Table According To Another
For some reason I fail to see an easy solution for this: I have two tables. Table1 is id. Table2 is id, Table1_id, Some_id.

Now, I want to find all id's from Table1 that DO NOT have a certain Some_id in Table2, AND id's from Table1 that do not appear in Table2 at all.

Example:

Table1 (id)
1
2
3

Table2 (id, Table1_id, Some_id)
1 | 2 | 6
2 | 2 | 4
3 | 1 | 4

The select statement uses Some_id as a parameter (querying from php) and should return the ids with conditions mentioned above. So, for example, if I do SELECT whatever .. Some_id = (or !=) 6, I would get Table1 ids 2 and 3. If Some_id would be 4 I'd get 1. If Some_id'd be 10, I'd get 1, 2 and 3. And I would get them only ONCE..

Unique Entry In A Table
I am having a problem with trying to work out how I could select the lowest unique number in a field called MONEY. I am trying to find the record with the least money, but no other record in that table can have the same amount of money.

Here is the table:

CREATE TABLE lubidEntries (
partnerid smallint(5) unsigned NOT NULL default '0',
lubidCamp smallint(5) unsigned NOT NULL default '0',
keyword varchar(40) NOT NULL default '',
MONEY varchar(40) NOT NULL default '',
status enum('current','old', 'lubid') default NULL,
time datetime NOT NULL default '0000-00-00 00:00:00',
numberid int(10) unsigned NOT NULL default '0',
KEY lubidentries (partnerid,status,numberid)
) TYPE=MyISAM;

UNIQUE Command In Create Table
I'm following a tuturial for SQL and it explains everything really well but I'm a little confused on some of the code which they wiz by.

Can you help explain what the UNIQUE UC_ is for in the following code:

CREATE TABLE Publisher(
PublisherSeq INT UNSIGNED NOT NULL AUTO_INCREMENT,
Name VARCHAR(80),
PRIMARY KEY (PublisherSeq),
UNIQUE UC_PublisherSeq (PublisherSeq),
UNIQUE UC_Name (Name));

Also is there a better site with a good refrence manual? I'm trying to find out what each command is used for and the diffrent options.

Making A Table With Unique Entries
I am trying to make a table with unique entries. Problem is, the entries
are upto 1000 characters long. Can I still apply a unique index and how and
if so, what field type should I choose?

Select From Table Using 2 Columns As Unique
How do I select from a single table using 2 columns (surname,postcode)as the unique criteria? e.g.

"Surname","Forename","Salutation","Postcode"
"Bear","Daddy","Mr","CT10 7AF"
"Bear","Mummy","Mrs","CT10 7AF"
"Bear","Baby","Miss","CT10 7AF"
"Piggy","Daddy","Mr","CT10 7AF"
"Piggy","Mummy","Mrs","CT10 7AF"
"Piggy","Baby","Miss","CT10 7AF"
"Piggy","Cousin","Ms","CT10 7AF"

would return just the following 2 rows:-

"Bear","Daddy","Mr","CT10 7AF"
"Piggy","Daddy","Mr","CT10 7AF"

I don't mind whether it's the first unique row or the last unque row. All I am interested in removing the duplicates.

Select From Table Using 2 Columns As Unique
How do I select from a single table using 2 columns (surname,postcode)
as the unique criteria?

e.g.

"Surname","Forename","Salutation","Postcode"
"Bear","Daddy","Mr","CT10 7AF"
"Bear","Mummy","Mrs","CT10 7AF"
"Bear","Baby","Miss","CT10 7AF"
"Piggy","Daddy","Mr","CT10 7AF"
"Piggy","Mummy","Mrs","CT10 7AF"
"Piggy","Baby","Miss","CT10 7AF"
"Piggy","Cousin","Ms","CT10 7AF"

would return just the following 2 rows:-

"Bear","Daddy","Mr","CT10 7AF"
"Piggy","Daddy","Mr","CT10 7AF"

I don't mind whether it's the first unique row or the last unque row.

2 Fields Combination Unique In Table...
So I need two unique fields in my table, but they should not be unique separately, just a combination of values in this two fields... Let's say I have table with fields book, page, text... I can have more then one book #1, more than one pages #1 but only one combination of book #1 and page #1! But I have page #1 for each book!

How to restrict this in MySQL?

Select From Table Using 2 Columns As Unique
How do I select from a single table using 2 columns (surname,postcode)
as the unique criteria?
e.g.

"Surname","Forename","Salutation","Postcode"
"Bear","Daddy","Mr","CT10 7AF"
"Bear","Mummy","Mrs","CT10 7AF"
"Bear","Baby","Miss","CT10 7AF"
"Piggy","Daddy","Mr","CT10 7AF"
"Piggy","Mummy","Mrs","CT10 7AF"
"Piggy","Baby","Miss","CT10 7AF"
"Piggy","Cousin","Ms","CT10 7AF"

would return just the following 2 rows:-

"Bear","Daddy","Mr","CT10 7AF"
"Piggy","Daddy","Mr","CT10 7AF"

I don't mind whether it's the first unique row or the last unque row.
All I am interested in removing the duplicates.

Unique Table Version Record
Unique table version record
Hello,
I am using a table record to store informations about database dump,I
use this table :

CREATE TABLE dump_version(
count BIGINT unsigned NOT NULL DEFAULT 0,
timestamp TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT 0
);
And I added a record (INSERT INTO dump_version () VALUES();) that I
modify each time I dump the database.
The only problem is that I can't ensure that this table will contain
only a single record, is that a way to ensure a king of 'UNIQUE' record
or something?

Help In Assigning Unique Keys To Table Entries
Allow me to share some design problem that I am facing.
I have the following table definition:

CREATE TABLE `image_info` (
`Image_ID` int unsigned not null,
`Title` varchar(255) default '' not null,
PRIMARY KEY (`Image_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
This simple table holds information about images. Each image has a title.
Note that the primary key was NOT defined as auto_increment (explanation below)
Also note that the table engine was defined as MyISAM (a system requirement)

The range of image IDs that can added into the table is split into 2:
1) IDs 1,000,000 and above are reserved for images that come from an external program
which defines the IDs.
2) IDs 1 - 999,999 are reserved for images uploaded by users in runtime in my site.

This means:
When I add an image from my application (1), I already know in advance what Image ID I need
to use and I can safely add it using:

insert into image_info (Image_ID, Title) values (1000010, 'I am a photo from an external program');
I know that image ID 1000010 is reserved just for that photo and so there won't be any duplicates.

When a user uploads a photo to my site (2), that photo needs to be stored in the table
with the next available ID which is < 1,000,000.
I use the following query:

select max(Image_ID) from image_info where Image_ID < 1000000;
I then add 1 to the value I am getting and this is the ID I will use for the image uploaded by the user.

The problem occurs when too many users are uploading photos at the same time.
This is a problem of atomicity. 2 Users may be assigned the same "free" image IDs and then
one of the queries will fail when adding the record to the table because of duplicate IDs.

I cannot change the order of the ID ranges (making 1 - 999,999 for my external program and 1,000,000 and above
for images uploaded by users), as this is a system requirement.
If I didn't have this requirement, I could have set auto_increment on the image_id field.

I am looking for a solution to my problem.
Can anyone think of one?
Maybe using an additional table which uses auto_increment to assign IDs?
I am developing in PHP, so maybe I need an application-level solution on top of MySQL

Returning All Unique Values From A Table Field
I wrote a select that works fine but I am looking at it and thinking that the query might require mysql to scan EVERY row before giving me my answer.

Lets pretend I have a table with fruits and want to return all the available colors of those fruits that I currently have in the database.

Is this an efficient or horribly inefficient statement?

"select color from fruits group by color"

It seems to return what I want:

fruits:
------
blue
green
yellow
...

I am just wondering if it goes through EVERY row and groups them?
If I have the table indexed by color am I allright?

ALTER TABLE DROP UNIQUE CONSTRAINT
I have a table with 4 columns, and I have defined a unique constraint on 3 of the columns.

Now, I need to add a 5th column to the table and define a new unique constraint, comprising the 3 old columns and the new column.

So my strategy is to drop the unique constraint, add a new column to the table, and then set the new constraints. I have no primary key defined on the table.

I could easily do this as follows in the older version of MySQL (4.0.X) as follows:

ALTER TABLE table_name DROP PRIMARY KEY; // This drops the first unique index, if there is no primary key defined.

However, in the later version that I have with me (4.1.10), I get the following error when executing the above command:

"ERROR 1091: Can't DROP 'PRIMARY'; check that column/key exists"

Is there any other way to achieve this? Basically, how does one drop a unique constraint?

Selecting Unique Values From A Table (NOT DISTINCT!)
Lets say we have a table that has the field photo_id and has values of it many times. For example 245 can exist any number of times (1, 5, 8, e.t.c.).

I want to select the values that exist in the table ONLY ONCE. Please note that i dont want to select each value once (using DISTINCT), that would return all the values that exist in the table.

For example lets say the table has the following values:

245
563
776
224
563
563
776
776
776
776

The query i want to make should return only the values 245 and 224 as 563 and 776 exist many times in that table.

Populating A List/menu With Table Data (non-unique)
I have a table (pictures) that has a unique field (pictureid) and several non-unique fields. I would like to populate a list box with the records that will likely have duplicates (username and galleryname).

I want to populate it in a way that there will be no duplicates in the list/menu, even though there are duplicates in the database. I can't seem to wrap my mind around the logic to get this done.

Create Table Error. #1064 - You Have An Error In Your SQL Syntax; Check The Manual That Corresponds
I have been trying to create two tables with the SQL below. I have the SQL in file and tried to import it to PhpMysqlAdmin in my control panel. The wierd thing is that the first table gets created while the second one is not created and error is thrown instead. The error message is

#1064 - 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 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

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?

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'),

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?

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?

Error #1103 - Incorrect Table Name Error?
I have always been able to copy my live database to my test database using "copy database" then my hosting company moved my site to a another server and now I can not copy my database anymore. My site works 100% with no problems but I cannot copy the database anymore

This is what I am doing:
I select "Copy database to" + "Structure and data" + "Add DROP TABLE" (like I have always done) but now it says a error "MySQL said: #1103 - Incorrect table name 'ABOUT'
I use MySQL version 4.1.21-standard & PHP version 4.4.4

I looked this error up and it said Error: 1103 SQLSTATE: 42000 (ER_WRONG_TABLE_NAME)

Actual error:

Error
SQL query:
DROP TABLE IF EXISTS `thebill_testspecmakers.com`.`ABOUT`
MySQL said: #1103 - Incorrect table name 'ABOUT'

I read that I have to upgrade to ver 5.1

Oracle, SQL server etc. conform to standard SQL in respect to table naming. MySQL only does from version 5.1.

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.

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?