Re: Left Join woes

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Michel Walsh (vanderghast_at_VirusAreFunnierThanSpam)
Date: 08/18/04 

Date: Wed, 18 Aug 2004 06:47:17 -0400

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

<anonymous@discussions.microsoft.com> wrote in message
news:828701c484fc$84141470$a401280a@phx.gbl...
> 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

Relevant Pages

  • RE: Executing Looping SP within DTS (SP provided)
    ... I haven't included the DTS Package. ... Package simply runs this procedure using execute sql task. ... INNER JOIN tbl_country cty ... --- Now load volume data by comparing the two months cumulative totals ...
    (microsoft.public.sqlserver.dts)
  • Re: VBScript Problem with Access Query (Unspecified error)
    ... absolutely fine when i execute the query in MS access 2003. ... Set conn = CreateObject ... JOIN AllEmployees ON SenderToDom.Sender = AllEmployees.Email) INNER JOIN ...
    (microsoft.public.scripting.vbscript)
  • Re: Update Query
    ... "vanderghast" wrote: ... You have to execute the update query for the ... if you 'execute' the query. ... UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN ...
    (microsoft.public.access.queries)
  • SQL Server 2000 UDF Intermittent Slow Execution
    ... Two identical SQL Server databases (DB1 backed up and ... to execute DB1.dbo.GetSchPaymentsTD took less than a millisecond ... identical code) after which the execution was lightning fast (just ... INNER JOIN dbo.tblPaymentTemplate PT ...
    (comp.databases.ms-sqlserver)
  • Re: Adapting Allen Brownes Sub-Query to do Year To Date and Month To Date in a financial year
    ... " You tried to execute a query that does not include the specified ... FROM tblSalesOrders AS A INNER JOIN AS ...
    (comp.databases.ms-access)