Re: Left Join Problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

sorry to be a nag, but it gives me a syntax error around [registration] in
the subquery. -- It says it's invalid join syntax.

Why is access seem so...nonstandard when it comes to SQL?


"John Vinson" <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ulhlb1dtfn9kf4q14db85n9o4u68bd9jmd@xxxxxxxxxx
> On Thu, 23 Jun 2005 08:24:19 -0400, "Eric Dropps"
> <edropps@xxxxxxxxxxx> wrote:
>
>>SELECT [sessions].[sessionid],[SessionName], [cost], [date]
>> FROM [sessions] LEFT JOIN [registration] ON
>>[sessions].[sessionid]=[registration].[sessionid] AND
>>[registration].[scoutid] = sid
>> WHERE scoutid IS NULL ;
>>
>>Please note that sid is a variable, not a field.
>>
>>The registration table has a compound PK, but I am only joining it on one
>>of
>>the keys (registration is an associative entity between sessions and
>>scouts,
>>and I don't need to know anything about the scouts except the scoutid
>>(which
>>is the PK))
>>
>>What I am trying to get from this query:
>>for a particular Scout (scoutId), show all session he HAS NOT registered
>>for.
>
> VBA variables are not visible to SQL queries. And you don't want to
> include the ScoutID in the JOIN clause in any case, since there *is*
> no join to it - it should be in the WHERE clause. I'd suggest a
> subquery:
>
> SELECT [sessions].[sessionid],[SessionName], [cost], [date]
> FROM [sessions]
> LEFT JOIN (SELECT SessionID FROM [registration]
> WHERE [ScoutID] = [sid])
> ON [sessions].[sessionid]=[registration].[sessionid]
> WHERE registration.sessionid IS NULL ;
>
> You'll need to use the Querydef's Parameters to pass the variable sid:
>
> Dim qd As DAO.Querydef
> Dim prm As Parameter
> Dim db As DAO.Database
> Set db = CurrentDb
> Set qd = db.Querydefs("your query name")
> qd.Parameters(0) = sid
>
> <open a recordset or whatever you want with the query>
>
> John W. Vinson[MVP]
>


.

Relevant Pages

  • Re: Left Join Problem
    ... > WHERE scoutid IS NULL; ... >the keys (registration is an associative entity between sessions and scouts, ... You'll need to use the Querydef's Parameters to pass the variable sid: ... Dim qd As DAO.Querydef ...
    (microsoft.public.access.tablesdbdesign)
  • Re: sessions numbers
    ... > select username, countfrom v$session group by username; ... Your count, from v$session, provides the number of sessions each ... SQL> select username, sid, count ...
    (comp.databases.oracle.server)
  • Re: Left Join Problem
    ... (I accidentally posted the MySQL version vs. the Access one with brackets). ... WHERE scoutid IS NULL; ... SessionID <-- Joined to sessions ...
    (microsoft.public.access.tablesdbdesign)