Table Alias

[rbma]

If table aliases are used, the aliases are in the popup menu and not the actual table names. In the Result tab, selecting any of the aliases generates a 'Table doesn't exist' error.

[Ritesh]

Can you provide me a sample query?

[rbma]

Ritesh wrote on Aug 16 2005, 05:26 PM:

Can you provide me a sample query?

[post=”6877″]<{POST_SNAPBACK}>[/post]

select n.id, ui.last_name, ui.first_name from name9 n, user_information ui WHERE n.web_login=ui.user_name and ui.date_entered='' and n.web_login<>'' order by n.id

[peterlaursen]

I don't think I have the infornation needed to understand your where-clause!

But to simplify things I created two tables “name9” and “user_information”

I have entered ID=1 and first_name = peter and last_name = laursen into one row.

all these 4 querries

select ui.id, ui.last_name, ui.first_name from user_information ui;

select n.id, n.last_name, n.first_name from name9 n;

select n.id, n.last_name, ui.first_name from name9 n, user_information ui;

select n.id, n.last_name, ui.first_name from name9 n, user_information ui where n.first_name = ui.first_name;

return

id last_name first_name

---

---

---

1 laursen peter

Quote:

In the Result tab, selecting any of the aliases generates a 'Table doesn't exist' error.

I don't understand. In the result tab I can select the table names form the drop-down-box, not the alias.

There is no misspelling ?

[rbma]

Please see attached thumbnail. Thanks.

Using the latest version v4.1

[peterlaursen]

Thanks! I saw it!

But what do you wnat me to do with it ?

My thumbnail is different ! No aliases here.

I am also using 4.1

[rbma]

peterlaursen wrote on Aug 16 2005, 06:37 PM:

Thanks!  I saw it!

But what do you wnat me to do with it ?

My thumbnail is different !  No aliases here.

I am also using 4.1

[post=”6881″]<{POST_SNAPBACK}>[/post]

Just wanted to show you that it is doing this on my end. Hopefully, Ritesh will have an answer. Thanks for your help.

[peterlaursen]

I added some colums to my tables so that I could run a qurey exactly like yours.

I cannot reproduce anything that you describe. See attached.

Lets start with the trad. questions 😛

MySQL version ??

Table definitions ??

Sample data ??

It also would be very nice if you had a look at the HISTORY-tab and copied in some of the more interesting-looking queries …

[rbma]

peterlaursen wrote on Aug 16 2005, 06:53 PM:

MySQL version ??

Table definitions ??

Sample data ??

It also would be very nice if you had a look at the HISTORY-tab and copied in some of the more interesting-looking queries …

[post=”6883″]<{POST_SNAPBACK}>[/post]

How do I find out the MySQL version?

name9 CREATE TABLE `name9` (

`ID` char(10) NOT NULL default '',

`MEMBER_TYPE` char(5) default NULL,

`TITLE` char(80) default NULL,

`COMPANY` char(80) default NULL,

`FIRST_NAME` char(20) default NULL,

`MIDDLE_NAME` char(20) default NULL,

`LAST_NAME` char(30) default NULL,

`SUFFIX` char(10) default NULL,

`DESIGNATION` char(20) default NULL,

`WORK_PHONE` char(25) default NULL,

`HOME_PHONE` char(25) default NULL,

`FAX` char(25) default NULL,

`TOLL_FREE` char(25) default NULL,

`MAIL_ADDRESS_NUM` int(10) default '0',

`BILL_ADDRESS_NUM` int(10) default '0',

`US_CONGRESS` char(20) default NULL,

`STATE_SENATE` char(20) default NULL,

`STATE_HOUSE` char(20) default NULL,

`DATE_ADDED` timestamp(14) NOT NULL,

`LAST_UPDATED` timestamp(14) NOT NULL,

`EMAIL` char(100) default NULL,

`WEBSITE` char(255) default NULL,

`web_login` char(60) NOT NULL default ''

) TYPE=MyISAM

user_information CREATE TABLE `user_information` (

`num` int(11) NOT NULL auto_increment,

`user_type` varchar(255) default 'Alumni',

`first_name` varchar(255) default NULL,

`last_name` varchar(255) default NULL,

`user_name` varchar(255) default NULL,

`password` varchar(255) default NULL,

`email_address` varchar(255) default NULL,

`user_number` varchar(255) default NULL,

`company` varchar(255) default NULL,

`website` varchar(255) default NULL,

`link_website` enum('No','Yes') default 'No',

`designation` varchar(30) default NULL,

`title` varchar(255) default NULL,

`work_phone` varchar(255) default NULL,

`home_phone` varchar(255) default NULL,

`fax` varchar(255) default NULL,

`toll_free` varchar(255) default NULL,

`mailing_preference` enum('Work','Home') default 'Work',

`billing_preference` enum('Work','Home') default 'Work',

`work_address` varchar(255) default NULL,

`work_city` varchar(255) default NULL,

`work_state` varchar(255) default NULL,

`work_zip` varchar(255) default NULL,

`work_country` varchar(255) default NULL,

`home_address` varchar(255) default NULL,

`home_city` varchar(255) default NULL,

`home_state` varchar(255) default NULL,

`home_zip` varchar(255) default NULL,

`home_country` varchar(255) default NULL,

`corporate_partner` enum('Yes','No') default 'No',

`education_level` varchar(255) default NULL,

`position` varchar(255) default NULL,

`age` varchar(255) default NULL,

`certification` varchar(255) default NULL,

`years_of_health_care` varchar(255) default NULL,

`years_of_radiology_business` varchar(255) default NULL,

`responsibilities` text,

`skill_set` text,

`services_offered` text,

`products_offered` text,

`date_entered` varchar(255) default NULL,

`status` enum('Active','New','Changed','Inactive') default 'Active',

`resp` text,

`skills` text,

PRIMARY KEY (`num`)

) TYPE=MyISAM

peterlaursen wrote on Aug 16 2005, 06:53 PM:

It also would be very nice if you had a look at the HISTORY-tab and copied in some of the more interesting-looking queries …

[post=”6883″]<{POST_SNAPBACK}>[/post]

/*[12:09:05 PM][ 0 ms]*/ show variables like '%character%'

/*[12:09:05 PM][ 0 ms]*/ show databases

/*[12:09:27 PM][ 110 ms]*/ show table status from `rbma`

/*[12:09:27 PM][ 0 ms]*/ use `rbma`

/*[12:09:31 PM][2437 ms]*/ select n.id, ui.last_name, ui.first_name from name9 n, user_information ui WHERE n.web_login=ui.user_name and ui.date_entered='' and n.web_login<>'' order by n.id

[peterlaursen]

to find MySQL-version execute this query

select version();

[rbma]

peterlaursen wrote on Aug 16 2005, 07:12 PM:

to find MySQL-version execute this query

select version();

[post=”6885″]<{POST_SNAPBACK}>[/post]

3.23.58-nt

[peterlaursen]

I don't have any chance to test with anything older than 4.0.24

I know that Ritesh has, so let's ask him to do that tomorrow morning.

I'll do a test with 4.0 now …

But it could very well be the implementation of alias'es with diferent MySQL-versions that causes the difference in behaviour with you and me!

[peterlaursen]

Confirmed.

As you described it with MySQL 4.0.24

My first test was with 5.0.11

For the test i crated this simplified case:

Code:

create database if not exists `test5`;

/*Table structure for table `name9` */

drop table if exists `name9`;

CREATE TABLE `name9` (
 `id` bigint(20) NOT NULL auto_increment,
 `web_login` varchar(255) default NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

/*Data for the table `name9` */

insert into `name9` values (1,'pl');

/*Table structure for table `user_information` */

drop table if exists `user_information`;

CREATE TABLE `user_information` (
 `id` bigint(20) NOT NULL auto_increment,
 `first_name` varchar(255) default NULL,
 `last_name` varchar(255) default NULL,
 `user_name` varchar(255) default NULL,
 `date_entered` varchar(255) default NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

/*Data for the table `user_information` */

insert into `user_information` values (1,'peter','laursen','pl','');

[peterlaursen]

yes then …

MySQL 3.23 and 4.0 behave one way and different than do 4.1 and 5.0 with the table alias. And that has effect on the function of select-dropdown-box in result-pane of SQLyog.

[Ritesh]

This issue is occuring due to that fact that MySQL version < 4.1 does not send information about the original table to the client (SQLyog) when aliases are used. The same query will work with v4.1 and above as they send SQLyog the correct information about the original table. For version < 4.1, you have to use query(s) without aliases.

[Ritesh]

BTW, table aliasing will not work with PHP tunneling as there is no way to get information about the original table using PHP-MySQL client library.

[Author]

Posts