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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
