List:General Discussion« Previous MessageNext Message »
From:John Hicks Date:July 5 2004 4:52pm
Subject:Re: another left join question - multiple "left join" statements
View as plain text  
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



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