Re: basic question about joins....
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]
Re: basic question about joins....
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]