Re: another left join question - multiple left join statements

2004-07-05 Thread John Hicks 
On Monday 05 July 2004 12:28 pm, bruce wrote:
 my test tbls

 cattbl dogtbl birdtbl
 namename   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]

Re: another left join question - multiple left join statements

2004-07-05 Thread Eric Bergen 
You only need to specify from tabel on the first table. Like this.

select 
 cattbl.name as cat, dogtbl.name as dog, birdtbl.name as bird
from dogtbl
left join cattbl on cattbl.id=dogtbl.catid
left join birdtbl on birdtbl.dogid=dogtbl.id;

-Eric

On Mon, 5 Jul 2004 09:28:02 -0700, bruce [EMAIL PROTECTED] wrote:
 my test tbls
 
 cattbl dogtbl birdtbl
 namename   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...
 
 i know how to get the results using where/and logic... but i'm trying to get a 
 better feel of the left join process...
 
 after looking at mysql/google, i'm still missing something...
 
 any comments/criticisms appreciated..
 
 thanks
 
 -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]