List:General Discussion« Previous MessageNext Message »
From:Wesley Furgiuele Date:July 5 2004 4:08am
Subject:Re: basic question about joins....
View as plain text  
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/mysql?unsub=1
>
>

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