Results 1 to 8 of 8
  1. #1
    mallorz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    5

    SQL - Outer Join

    Shouldn't this work? I am trying to select all customers, whether they have placed an order or not. (Using Access 2010)

    SELECT fname, lname, Order.id


    FROM Customer OUTER JOIN Order
    WHERE Customer.id = Order.cust_id;

    I have also tried INNER JOIN, =*, *= and changing the WHERE to ON. All return a syntax error in FROM clause.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I think you need LEFT or RIGHT keyword. Review http://msdn.microsoft.com/en-us/libr...=sql.105).aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mallorz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    5
    Thank you for your reply. I have tried both LEFT OUTER JOIN and RIGHT ... and I get the same results. As does simply 'LEFT JOIN' and 'RIGHT JOIN'

    I don't get it because I am copying an example straight from a book!

  4. #4
    mallorz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    5
    Looking at the code from that link I think I may not be in the right forum here. Nonetheless, if someone has an idea I'd be very grateful.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Instead of WHERE keyword use ON.

    Use Access query designer to help build query then view the SQL statement in SQL View.

    The WHERE could be used without JOIN clause to link on pk/fk. With JOIN clause use ON keyword and then WHERE can be used for filter criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    mallorz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    5
    Hmm.. I thought I tried that! Thanks, it ran the query without an error. Returned 600 some results, which is wrong, but at least I didn't get the error! Just have to insert more requirements somehow or what not.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Glad you have progress.

    Sites that discuss SQL syntax are pertinent to Access. Access uses SQL to build queries. There are some SQL features that Access can't process but doubt you will miss them.

    Review http://w3schools.com/sql/default.asp
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    mallorz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    5
    Thank you again for your assistance, and for that link, I bookmarked it for future reference. I guess I completed my goal of completing one SQL query for the night. I have a total of 3 left now on this assignment and this is the first time that I find them difficult. I'll tackle another one tomorrow, right now I am very tired and off to bed.

    Have a good night!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Left-Outer Join on Non-Unique ID
    By defaultuser909 in forum Queries
    Replies: 2
    Last Post: 09-06-2012, 10:16 AM
  2. OUTER and INNER JOIN Issue
    By riaarora in forum Queries
    Replies: 1
    Last Post: 09-02-2012, 08:13 AM
  3. Outer Join Nested in Inner Join
    By Stevens7 in forum Queries
    Replies: 2
    Last Post: 10-19-2011, 01:34 PM
  4. Outer Join Composite Key
    By Lady_Jane in forum Queries
    Replies: 6
    Last Post: 08-26-2011, 09:44 AM
  5. I can't get a full outer join to work
    By Bobt1993 in forum Queries
    Replies: 3
    Last Post: 03-20-2010, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums