Bruce:
The problem is that you have universityTBL listed twice in your list of
tables. Here is your statement, with numbers for each of the sources.
SELECT schoolTBL.name, universityTBL.name
FROM (1) universityTBL, (2) schoolTBL
LEFT JOIN (3) universityTBL ON schoolTBL.universityID =
universityTBL.ID
WHERE universityTBL.name = 'sam'
The syntax I think you want is:
SELECT schoolTBL.name, universityTBL.name FROM schoolTBL LEFT JOIN
universityTBL ON schoolTBL.universityID = universityTBL.ID WHERE
universityTBL.name = 'sam'
Wes
On Jul 4, 2004, at 11:36 PM, bruce wrote:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
