Re: basic question about joins....

[Wesley Furgiuele]

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]