MYSQL :: Strange Alias And Join Problem On Server But Not Dev Box

Strange Join
I'm hoping someone can help me here.. I have 2 tables..

car (id, driver, navigator, backup)
person (id, name, age, gender)

The driver, navigator, and backup fields are FK's for person ids.

What I want is:
'driver', joe, 27, M
'navigator', sue, 29, F
'backup', jack, 32, M

Is there a way to do this in a single sql statement?

Strange LEFT JOIN
I tried a command (from phpMyAdmin):

SELECT * FROM dttips t left join dtbets b on t.datum=b.datum and t.userid=b.userid where t.tip not like '0000%' and t.datum like '2004%' and (b.sazka='000' or isnull(b.sazka)) and t.userid='pepa'

I've got 30 records in table t, 18 records in table b (with right key), but always get only 18 records !!! I would expect 30 lines (with LEFT JOIN) - 18 regular and 12 with 'b' table NULL 'tail'!!!

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?

Strange Password Setting Behavior In Fedora Core 4 (server 4.1.11)
I have tried adding users aided by several references and this typifies the procedure:

http://dev.mysql.com/doc/mysql/en/adding-users.html

But, I found that after adding them, I could not use them to get into mysql. I found my way to this reference,

http://dev.mysql.com/doc/mysql/en/access-denied.html

and on a hunch, set the password to '', that is, no password at all and find that now I can use them to get in. What is going wrong here? Also, I am finding that I cannot get into my database from a remote host, even though I use the '%' variable or even more directly, the actual 'RemoteHost' name.

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

Cannot Alias Locked Tables / Join To Unlocked Tables ?
If you manually issue a table lock then query that table, aliasing the table generates an error. If you try to join the table to another table that is not locked, you will receive an error. What is the reason for this?

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?

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.

Strange DB Error
I have a strange error from MySQL:

Could not execute SELECT en FROM translation WHERE id = 229. Error: 1033 :: Table './dbname/translation' was created with a different version of MySQL and cannot be read.
I just got this error like that, without doing nothing. And the bad news is that the entire DB is wracked. What can I do?

Strange Character ...
We were trying to save https://oururl in mysql database.
But got https: / /oururl

We don't know why "/" be changed to " /", any suggestion?

Strange Insert
i hava a basic insert sql: insert into test values(123.34, 321.32, 678.34,32.8);

Here is table structure:

CREATE TABLE `test` (
`data_hba1c` float(3,2) unsigned NULL,
`data_albumiini_kreatiini` float(4,3) unsigned default NULL,
`data_mikroalbuminuria` float(3,2) unsigned default NULL,
`data_sldlkol` float(2,1) unsigned default NULL
) ENGINE=InnoDB;

MySql changes inserted values in database to:

data_hba1c -> 9.99
data_albumiini_kreatiini -> 9.999
data_mikroalbuminuria -> 9.99
data_sldlkol -> 9.9

Strange but true. Any one else had the same problem?

Strange Connection
I'm using MySQL Control Center to connect to a MySQL server which I have configured and started on a remote computer. I have already succeeded connecting and started working on the "test" database. I already had 1 table there, and when I tried to return all its rows, it suddenly could not connect and retrieve the data set (error 2003)! I then disconnected from the database and tried to
re-connect, but then I recieved again error 2003! Then I connected to the remote computer (using SSH) and restarted the MySQL server. After that, I was able to connect to the "test" database. I created a new table, added a few fields and saved it. Then I tried to return all the rows of the new table, and once again got error 2003!!! I tried executing the SQL query "SELECT * FROM <table-name>", but got the same results.

I don't understand what's happening here! Why can I define tables, but not filling them with data? Why, after disconnecting from the database, can't I connect again?

Strange Float
I have a field, with a FLOAT(31,30) as field type, whenever I insert

5.254 the value changes to 5.254000186920166015625000000000

Where do these numbers after the .254 come from, I would logically think it would fill it up with zero's or nothing .. but this is a real problem since quite some calculations depend on these numbers and there is no room for errors in the needed calculations, and Yes, we need calculations up to 30 numbers after the .

Strange Query
I have the following simple query: select * from person where username='admin' and status='A'. When executed in the person table it executes fine but returns no recordset. In a similar table (user with a username and status field) it executes as expected and returns a result. The problem seems to be the status field (is it a "reserved" word. The only difference between the two tables is that in the user table - username is unique. Does any one have any idea why this behaviour is exhibited?

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?

Strange Cartesian Product
I have a strange cartesian product error. I am doing a query from a db I did not create. I can see the tables in it and have a search query that searches in 2 tables. If I search for "name LIKE ó“%' OR model LIKE '%670%' " I get the cartesian product error.

However, if I search for "name LIKE ó“%' " by itself I get an empty query set, 0 rows returned.
And model LIKE '%670%' pdoduces an expected query of 7 rows.

This makes absolutely no sense to me. If it does not match anything in the name column, how the heck is it creating a cartesian product result???

Furthermore, if I change the query to something that is contained in the name column, then I don't get the error.

This is driving me nuts. To compund the matter further there is a third column involved which is doing the same thing. But I am currently testing this with just the two columns to narrow things down a bit.

Strange Problem With Primary Key
I am unable to add records in tut_db table, its stopped on id 255. Whenever I tried to add a new record I received the message "#1062 - Duplicate entry 饗' for key 1". I wonder why I got this message, it is an auto increment key.
Following is the structure of table

PHP

`id` tinyint(5) unsigned NOT NULL auto_increment,
`s_cat_id` int(4) NOT NULL default Ɔ',
`title` varchar(255) collate latin1_general_ci NOT NULL default '',
`url` varchar(255) collate latin1_general_ci NOT NULL default '',
`des` text collate latin1_general_ci NOT NULL,
`date` date NOT NULL default 񟍰-00-00',
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`)

Remove Strange Characters ('~!@#`)
is there anyway to remove those characters , '~`# when inserting into the database?

Replace Strange Characters
When I made a back-up on my old database and did restore it on a another server then all Ã¥ Ã¤ Ã¶ in all tables where replaced with some strange characters.

How can I use Replace() to replace all these characters in the database (all tabels at the same time)

Sha1 Strange Values
I'm trying to use Sha1() to store a password value in our software. The software is using MyODBC 3.51.10. MySQL version 4.1

Statement is simply:
update mytable set mypass = sha1('thepassword') where myuser = 1

For some reason, when one particular user saves a password, it stores a value that is exactly half of what it should be. It literally stores the first 20 characters of the value.

If I store the same password, I get the full value.

The password is stored in a varbinary column. Used to be in a char column. Both had the same problem (I changed the column type thinking that would help.

Any ideas? I can't find any reason he would get a different value for the same password using the same software, database, server, etc.

We're both running Windows XP. I'm almost certain there is something unique about his machine, but I can't imagine what it could be!

Weird / Strange Characters
I recently switched servers, and when i dumped all of my database info, some of the fields that contained single and double quote characters turned into:Ã¢â‚¬â€. (things like this). Does anyone know of an easy way to fix this. Possibly without re importing the data.

Strange Codes Have Appeared! =o(
we all know what access databases are! ;oP

So I converted it to a MYSQL database. I only deal with the webside of the site as opposed to the content, although the guy who does this reported that there were some strange characters which had cropped up (not sure if this was related to the Access changeover or not).
For example some of the ones we have found so far (first is what it should be, then the code which the database has converted it to):

' IS CONVERTED TO Ã¢â‚¬â„¢
-- IS CONVERTED TO Ã¢â‚¬â€
- IS CONVERTED TO Ã¢â‚¬Â¢

At the moment i just have a replace in my asp code when displaying these characters, but he says that more are appearing!?

Should this be happening (as to me it seems like all these characters are used in SQL statements) or is there a problem somewhere along the line?

Strange Error On A Mysql_query
When I run the following query through PHPMYADMIN I have no problem and return the proper results. When I run a script calling mysql_query, I recieve the following errror: Warning: Supplied argument is not a valid MySQL result resource on line 58.

To troubleshoot the situation: I echo the $sql that is being processed. Then I copy what is echoed, and paste it into PHPMyAdmin, and run it, and have no problems. Nontheless, I continue to recieve the error message when I try to run the PHP script and use the query.

I'm using the following SELECT query:

Code:

SELECT IfNULL (a.article_date_added, p.photo_article_date_added) AS date_added,
IFNULL (a.article_type, p.photo_article_type) AS type,
IFNULL (a.article_title, p.photo_article_title) AS title,
IFNULL (a.article_deck, p.photo_article_deck) AS deck,
IFNULL (a.article_author, p.photo_article_author) AS author,
IFNULL (a.article_date_long, p.photo_article_date_long) AS date_long
FROM _dummy AS D
LEFT JOIN article AS a ON ( D.num =0 AND a.article_section_id =11 )
LEFT JOIN photo_article AS p ON ( D.num =1 AND p.photo_article_section_id =11 )
WHERE D.num < 2
AND IFNULL(a.article_date_added, p.photo_article_date_added) IS NOT NULL
AND IFNULL(a.article_title, p.photo_article_title) IS NOT NULL
AND IFNULL(a.article_type, p.photo_article_type) IS NOT NULL
AND IFNULL(a.article_deck, p.photo_article_deck) IS NOT NULL
AND IFNULL(a.article_author, p.photo_article_author) IS NOT NULL
AND IFNULL(a.article_date_long, p.photo_article_date_long) IS NOT NULL
ORDER BY date_added DESC

Strange Query Result
I have a column (tinyint) that has either 1s or 0s in it. When I run this query counting either 1s or 0s it counts all of the rows, not just the ones with 1s or 0s as stated in the query:
SELECT COUNT( cb_paiddues = '0' ) from jos_comprofiler
or
Code:

SELECT COUNT( cb_paiddues = '1' ) from jos_comprofile

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

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

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

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 'unknown Column' Error
PHP

$sql = "SELECT COUNT(*) FROM klanten WHERE username = '$_POST[newid]'";
    $result = mysql_query($sql);
    if (!$result) {
        error('A database error occurred in processing your '.
              'submission.If this error persists, please '.
              'contact xxx.' . mysql_error());

and I swear there is a column called username. Still, I get an error saying that column username is not there.

Anyone has an idea what might be wrong ?

I just added the column. Does the server need time to refresh or sth ?

Strange Select Command, Is That Normal?
I am using addslashes before submitting a string to mysql. Means, O'REILLY is getting submitted as O'REILLY.

Now I was using a select command.
PHP

$lname = addslashes(htmlspecialchars($_POST['lname']));
"SELECT * FROM $mytable WHERE lastname = '$lname'"

Problem is that, it's not working.

So I made a function, and after much hit and try, this is what I found.
PHP

function kgbaddslashes($string) {
$string = str_replace("'", "\'", $string);
return $string;
}

and then
PHP

$lname = addslashes(htmlspecialchars($_POST['lname']));
$lname = kgbaddslashes($lname);
"SELECT * FROM $mytable WHERE lastname = '$lname'"

Now, this is working, but I am wondering where's the problem. I mean, why I had to add so many slashes to make it work?

Strange Characters In Mysql Database
I'm currently updating a site and in one of the fields is the content, but in the content I'm noticing strange characters such as:

Quote:

Ã¢â‚¬Å“This is a quoteÃ¢â‚¬?
These are Mr PlumÃ¢â‚¬â„¢s ideas...

The thing is, I need to get rid of them, or replace them (on mass) with the proper character. Is there a way to do this without finding every record that has one of these special characters and then replacing it?