hi...
i'm trying to figure out how to deal with joins (left/right)..
i have the following test tbls...
create table universityTBL(
name varchar(50) not null default '',
ID int(10) not null auto_increment,
primary key (ID),
unique key (name)
)type =MyISAM;
create table schoolTBL(
name varchar(50) not null default '',
universityID int(10) not null,
ID int(10) not null auto_increment,
primary key (ID),
unique key (name, universityID)
)type =MyISAM;
mysql> describe universityTBL;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| name | varchar(50) | | UNI | | |
| ID | int(10) | | PRI | NULL | auto_increment |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> describe schoolTBL;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| name | varchar(50) | | MUL | | |
| universityID | int(10) | | | 0 | |
| ID | int(10) | | PRI | NULL | auto_increment |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from universityTBL;
+------+----+
| name | ID |
+------+----+
| sam | 1 |
| bed | 2 |
+------+----+
2 rows in set (0.00 sec)
mysql> select * from schoolTBL;
+----------+--------------+----+
| name | universityID | ID |
+----------+--------------+----+
| medicine | 1 | 1 |
+----------+--------------+----+
i want to be able to produce a select where schoolTBL.universityID = university.ID.
i can get the results using a straight select with a where" and a "and" clause:
this works...
mysql> select s1.name,u1.name
-> from universityTBL as u1, schoolTBL as s1
-> where u1.ID=s1.universityID
-> and u1.name='sam';
+----------+------+
| name | name |
+----------+------+
| medicine | sam |
+----------+------+
1 row in set (0.00 sec)
however, i'm trying to get the results using a join. i've tried the follwoing with no
luck..
mysql> select schoolTBL.name, universityTBL.name
-> from universityTBL , schoolTBL
-> left join universityTBL on universityTBL.ID=schoolTBL.universityID
-> where universityTBL.name='sam';
ERROR 1066: Not unique table/alias: 'universityTBL'
any thoughts/comments as to what's wrong... it's got to be something basic... perusing
through google/mysql/etc.. hasn't shed any light on where the issue is...
thanks for any comments/criticisms/etc...
=bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
