Results 1 to 8 of 8
  1. #1
    bcz is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2019
    Posts
    12

    Trouble with LEFT JOIN due to nested INNER JOIN

    I am pretty much an Access and SQL newbie. I have Access 2019. I have a query from multiple tables. Most of my joins are inner joins, but I need one left or right and I can't get it to work. Here are the tables in my query:

    Click image for larger version. 

Name:	ReadingList-WorkChooser-Capture-20200703.JPG 
Views:	29 
Size:	101.9 KB 
ID:	42338


    Here is a draft version of my query:

    SELECT [tblAuthor]![Surname] & ", " & [tblAuthor]![GivenName] & " " & [tblAuthor]![OtherName(s)] AS FullName,
    tblSection.Section, tblAuthor.BillsIntent, tblAuthor.RatingGoodreads AS tblAuthor_RatingGoodreads,
    tblWork.Title, tblWork.YearPublished, tblWork.RatingGoodreads AS tblWork_RatingGoodreads,
    tblReading.FormatHeld, tblReading.[Own?], tblReading.[Read?],


    tblSeries.SeriesName, tblmmSeriesWork.OrderInSeries
    FROM ((tblWork
    INNER JOIN ((tblAuthor INNER JOIN tblmmAuthorWork ON tblAuthor.[AuthorID] = tblmmAuthorWork.[AuthorIDFK]) INNER JOIN tblSection ON tblAuthor.[SectionTypicalIDFK] = tblSection.[SectionID])
    ON tblWork.[WorkID] = tblmmAuthorWork.[WorkIDFK])
    INNER JOIN tblReading ON tblWork.[WorkID] = tblReading.[WorkIDFK])
    INNER JOIN (tblSeries INNER JOIN tblmmSeriesWork ON tblSeries.[SeriesID] = tblmmSeriesWork.[SeriesIDFK]) ON tblWork.[WorkID] = tblmmSeriesWork.[WorkIDFK]
    ORDER BY tblAuthor.BillsIntent;

    This query works as far as it goes. However, the series info is not populated for every book (I use "work"). I don't want to limit the output to only rows where a series-work relation is populated in the many-to-many table tblmmSeriesWork. My naive approach was to use a LEFT JOIN to bring in the tblSeries stuff: "LEFT JOIN (tblSeries INNER JOIN tblmmSeriesWork ON...", but that fails, I guess because of the following INNER JOIN I take it. If I replace the series joining stuff with
    "LEFT JOIN tblmmSeriesWork ON tblWork.[WorkID] = tblmmSeriesWork.[WorkIDFK]"
    that works - I'm getting the output rows I want - but that just gets me an autonumber ID for Series, and I want tblSeries.SeriesName.
    How can I fix my query to allow for blank Series.Name values in the output? Thanks.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There are two obvious approaches
    1. Use your left join as suggested and use Nz(SeriesName,"") to handle the missing values
    2. Use a union query made up of two parts. First part is your original query with an inner join. Second part is an unmatched query to catch your missing series names
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Consider fixing your field names before progressing much further.
    Do not use spaces, punctuation or special characters in object names.
    You have field names: "OtherName(s)", "Own?" and "Read?".

    Also, "Section" is a reserved word in Access (JET reserved (kb248738);ODBC (kb125948);ANSI-92 Reserved (kb287417)).

  5. #5
    bcz is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2019
    Posts
    12
    isladogs, thank you for the suggestions. But, as I said I am new to Access. In your first suggestion, where do I put the "Nz(SeriesName,"")" in my query? I don't see how it will avoid the error from having an INNER JOIN inside a LEFT JOIN. Thanks.

    ssanfu, thanks for the advice. I'll put these on my to-do list.


  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by bcz View Post
    isladogs, thank you for the suggestions. But, as I said I am new to Access. In your first suggestion, where do I put the "Nz(SeriesName,"")" in my query? I don't see how it will avoid the error from having an INNER JOIN inside a LEFT JOIN. Thanks

    You would add an extra field in your query e.g. Series_Name: Nz(SeriesName,"") and use a LEFT JOIN between those two tables.
    Or, as previously suggested, use a UNION query.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    bcz is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2019
    Posts
    12
    Well, it took me a while, but I finally got the union query approach to work. For the second query, first I had to figure out how to insert blank columns where the series info would go, then I had to figure out how to only include author-title rows that did not have an associated series name.
    Thanks isladogs for pointing me in the right direction.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome. Congratulations on sorting it out
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. INNER JOIN vs LEFT OUTER JOIN
    By shylock in forum Access
    Replies: 3
    Last Post: 10-16-2018, 09:38 AM
  2. Replies: 3
    Last Post: 09-20-2017, 09:50 AM
  3. Replies: 17
    Last Post: 01-26-2014, 06:53 AM
  4. Inner Join works but not Left Join
    By kwooten in forum Queries
    Replies: 1
    Last Post: 12-11-2012, 05:09 PM
  5. Outer Join Nested in Inner Join
    By Stevens7 in forum Queries
    Replies: 2
    Last Post: 10-19-2011, 01:34 PM

Tags for this Thread

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