Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    4

    Unanswered: Joining four tables,one of them to be left joined

    Hello!
    I am a regular mysql/php developer.I am having problem in joining multiple tables in MS Access 2003.
    The three tables are to be cross joined while the fourth is to be left joined.All The tables have a unique tabnumber to join each other with.

    The query goes something like this-

    SELECT a.*, b.*, c.*, d.*
    FROM tabA AS a
    join tabB AS b ON a.tabnum = b.tabnum
    join tabC AS c on b.tabnum = c.tabnum
    left join tabD as d on c.tabnum = d.tabnum

    Can anybody please tell me te correct way to write this query so that MS Access can understand this it.

    Thanx

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you sure they should be cross joined?
    I believe if you ommitt the join type it assumes an INNER JOIN (but I could be wrong!)
    Code:
    SELECT *
    FROM   tabA a 
     INNER
      JOIN tabB b
        ON a.tabnum = b.tabnum
     INNER
      JOIN tabC c
        ON b.tabnum = c.tabnum 
     LEFT
      JOIN tabD d
        ON c.tabnum = d.tabnum
    Last edited by gvee; 09-05-07 at 07:52. Reason: ommitt negate
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    I believe if you negate the join type it assumes an INNER JOIN (but I could be wrong!)
    again with the negate!!!

    also, in order not to guess, one would test, eh george? which version of access do you have?


    Code:
    SELECT anything
         , just_not_the_dreaded_select_star
      FROM ((
           tabA a 
    INNER
      JOIN tabB b
        ON b.bar = a.foo
           )
    INNER
      JOIN tabC c
        ON c.qux = b.fap
           )
    LEFT OUTER
      JOIN tabD d
        ON d.rap = c.gop
    notice the parentheses

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry Rudy - I had the meanings of the two words confused... amended my post above (and in previous thread too) to be correct.
    Code:
    SELECT anything
         , just_not_the_dreaded_select_star
    *Lol*
    George
    Home | Blog

Posting Permissions

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