List:General Discussion« Previous MessageNext Message »
From:John Hicks Date:July 5 2004 6:32am
Subject:Re: basic question about joins....
View as plain text  
On Sunday 04 July 2004 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

You were using a join with your "straight select": an 
inner join. 

What you seem to be looking for is a left join. 
Wesley's post speaks to that point.

(The difference is, the inner join would not give you 
results for schools that did not have a university 
associated with them. A left join will give you 
results for such schools, with nulls in the columns 
for the university.)

--John

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