List:General Discussion« Previous MessageNext Message »
From:bruce Date:July 5 2004 3:36am
Subject:basic question about joins....
View as plain text  
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



Thread
mySQL LIMIT and ORDER BY Problem???Gary Mack4 Jul
  • Re: mySQL LIMIT and ORDER BY Problem???John Hicks5 Jul
    • basic question about joins....bruce5 Jul
      • Re: basic question about joins....Wesley Furgiuele5 Jul
      • Re: basic question about joins....John Hicks5 Jul
        • another left join question - multiple "left join" statementsbruce5 Jul
          • Re: another left join question - multiple "left join" statementsJohn Hicks5 Jul
          • Re: another left join question - multiple "left join" statementsEric Bergen5 Jul