How to prevent ambiguous outer joins error message

[craigugoretz]

Hello,



I have the following query:

SELECT Media.Title, Author.[Last name], Author.[First and middle name]
FROM Author INNER JOIN (Media LEFT JOIN [Author/Media] ON Media.ID = [Author/Media].[Media ID]) ON Author.ID = [Author/Media].[Author ID];

I get this error message: The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and include that query in your SQL statement.

There are three tables: Media, Author/Media, and Author. There is a one to many relationship between Media and Author/Media and there is also a one to many relationship between Author and Author/Media. There is actually a many to many relationship between Media and Author, and Author/Media is the cross reference table.

Media has two fields: ID and Title. Author has three fields: ID, [Last name], and [First and middle name]. Author/media has three fields: ID, Author ID, and Media ID. There is a left join between Media.ID and Author/Media.[Media ID]. There is an inner join between Author.ID and Author/Media.[Author ID].

My goal is the following: if the Media table does not have associated Authors, display those records from the Media table (Title field) anyway with the Author's [Last name] and [First and middle name] fields listed as NULL or blank. Otherwise, display each unique combination of Title.Media, Author.[Last name], and Author.[First and middle name]. This all of course involves the the cross reference table.

What must I do to not get the error message?

Sincerely,
Craig

[NTC]

so in the most general and non technical terms; if one of your join lines is an arrow then, depending on the query design, the other join line must also be an arrow typically going the same direction. very easy to try.

however this doesn't always return the results that you seek. So you do want to keep in mind that a query can be built using another query as its record source. so doing things in steps like this may also be your solution