On Monday 05 July 2004 12:28 pm, bruce wrote: > my test tbls > > cattbl dogtbl birdtbl > name name name > id -------> catid ---> dogid > id ---- id > > so > dogtbl.catid = cattbl.id > birdtbl.dogid = dogtbl.id > > my question is how i can use "left joins" to produce > the results set with the names of all three > cat/dog/bird... > > i've tried various derivatives of the following... > mysql> select cattbl.name as cat, > -> dogtbl.name as dog, birdtbl.name as bird > -> from dogtbl > -> left join cattbl on > cattbl.id=dogtbl.catid -> from birdtbl > -> left join dogtbl on > birdtbl.dogid=dogtbl.id; > > i keep getting an error complaining about the 2nd > "from/left join"... ... > -bruce
It's not really clear from the manual, but if you check the basic syntax of the SELECT statement: http://dev.mysql.com/doc/mysql/en/SELECT.html and the JOIN: http://dev.mysql.com/doc/mysql/en/JOIN.html you'll see that all the tables references (the FROM and the JOINS) go in a single spot in the SELECT statement: SELECT <column list> FROM <table references> WHERE <conditions>. So there should only be a single FROM followed first by the tables in the basic select (including any inner join) and then by any LEFT or RIGHT JOINs you wish to add. Generally, you only reference each table one time. select cattbl.name, dogtbl.name, birdtbl.name from dogtbl left join cattbl on cattbl.id=dogtbl.catid this is wrong (see comments above) -> from birdtbl left join anothertablenametoaddtoyourquery on birdtbl.dogid=dogtbl.id; Good luck, Joihn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
