Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: several outer joins on the same table in access

    Hi,
    I'm trying to outer join the same table twice ir more like that:

    "SELECT author,
    co_author_1,
    s.bio_url AS au_bio,
    s1.bio_url AS co1_bio
    FROM articles AS a
    LEFT JOIN staff s ON (a.author = s.person_name)
    LEFT JOIN staff s1 ON (a.author = s1.person_name)
    WHERE article_section='commentary' ORDER BY article_date DESC"

    and I get the following error :

    Microsoft JET Database Engine (0x80040E14)
    Syntax error (missing operator) in query expression '(a.author = s.person_name) LEFT JOIN staff s1 ON (a.author = s1.person_name)'.

    what os the correct way to do this
    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need to parenthesize two tables at a time whenever there are more than two tables in the query:
    PHP Code:
    select author
         
    co_author_1
         
    article_date
         
    s.bio_url as au_bio
         
    s1.bio_url as co1_bio
      from 
    (
           
    articles as a
    left outer
      join staff s 
        on a
    .author s.person_name
           
    )
    left outer
      join staff s1 
        on a
    .author s1.person_name
     where article_section 
    'commentary' 
    order 
        by article_date desc 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    thank u so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •