Left Join woes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Extract from db that I am trying to construct query on:

Tables:

Issue
Issue_Note
Note

Relationships:
Issue one-to-many Issue_Note - joined on IssueKey
Issue_Note one-to-many Note - joined on NoteKey

As you can see the the Issue_Note table is basically a
link table holding details of the notes for a specific
issue.

I have written the sql I am trying to create in T-SQL
syntax below as I appear unable to create it within access

SELECT i.*, n.*
FROM Issue AS i
LEFT JOIN Issue_Note AS in ON i.IssueKey=in.IssueKey
INNER JOIN Note AS n ON in.NoteKey=n.NoteKey

Can anybody help me get a working access query to do this.

Nathan
 
Extract from db that I am trying to construct query on:

Tables:

Issue
Issue_Note
Note

Relationships:
Issue one-to-many Issue_Note - joined on IssueKey
Issue_Note one-to-many Note - joined on NoteKey

As you can see the the Issue_Note table is basically a
link table holding details of the notes for a specific
issue.

I have written the sql I am trying to create in T-SQL
syntax below as I appear unable to create it within access

SELECT i.*, n.*
FROM Issue AS i
LEFT JOIN Issue_Note AS in ON i.IssueKey=in.IssueKey
INNER JOIN Note AS n ON in.NoteKey=n.NoteKey

Can anybody help me get a working access query to do this.
Click to expand...
Hi Nathan,

In Access you cannot nest an Inner Join within an Outer Join.

There are 2 typical strategies:

1) Create a query that Inner Joins Issue_Note w/ Note.
Then...Left Join Issue to this query.

2) Use this Inner Join as a subquery that you Left Join Issue.

SELECT i.*, q.*
FROM Issue AS i
LEFT JOIN
(SELECT
n.*,
in.IssueKey
FROM
Issue_Note AS in
INNER JOIN Note AS n
ON in.NoteKey=n.NoteKey) As q
ON i.IssueKey = q.IssueKey

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Hi,


That join is ambiguous, meaning if you execute the inner join first, then
the left join, you get a different result than if you do the left join
first, then the inner join. It would NOT be an ambiguous join if the inner
join would NOT involved the unpreserved side of the left join... but the ON
condition of the inner join actually implies the right table of the left
join... so it is an ambiguous join.


Theory aside, with Jet, you cannot use the parenthesis to indicate the order
of execution. Basically, you make two queries, the first one make the first
join you want, the second query use the first query, as table, and execute
the last join. You can do it in one query, using virtual table.

If you meant the inner join, then the outer join:

SELECT i.*, x.*
FROM issues As i LEFT JOIN

( SELECT n.*, in.issueKey
FROM issue_Note AS in
INNER JOIN note AS n
ON in.NoteKey=n.NoteKey)
As x

ON i.IssueKey=x.IssueKey



If you meant the reverse, you probably can do better with just inner join,
since the first left join would make some in.NoteKey values NULL (if not,
then again an inner join is enough) and the last join, an inner join on
those NULL would disregard them anyhow. So:

SELECT i.*, n.*
FROM ( Issue AS i INNER JOIN Issue_Note AS in
ON i.IssueKey=in.IssueKey
) INNER JOIN Note AS n
ON in.NoteKey=n.NoteKey


should do. Jet (seems to) require the parenthesis to "group" the ON with the
JOIN it is to be applied, explicitly (even if it can live without, in some
cases, but I never studied the patterns).



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top