Left Join Problem

[Eric Dropps]

Hello, trying to code this query in access--it keeps saying it can't do it!

This is a MySQL query, and it worked--perfectly

SELECT `sessions`.`sessionid`,`SessionName`, `cost`, `date`
FROM `sessions` LEFT JOIN `registration` ON
`sessions`.`sessionid`=`registration`.`sessionid` AND scoutid= sid
WHERE scoutid IS NULL ;


In this query, I want a list of sessions the user has NOT registered for
(aka no entry in registration table)

Tables:

sessions registration
scouts (not used in above query)
-------------- -------------------
--------------------
sessionid (PK) <---------> sessionid (PK,FK1) |-->
scoutid(PK)
other field N/A scoutid (PK,FK2) <---------|
other fields N/A


Can access not handle complex queries? Do i have to use a
*cough*slow*cough* subquery?

Thanks!

-Eric Dropps

 

[John Vinson]

Hello, trying to code this query in access--it keeps saying it can't do it!

This is a MySQL query, and it worked--perfectly

SELECT `sessions`.`sessionid`,`SessionName`, `cost`, `date`
FROM `sessions` LEFT JOIN `registration` ON
`sessions`.`sessionid`=`registration`.`sessionid` AND scoutid= sid
WHERE scoutid IS NULL ;


In this query, I want a list of sessions the user has NOT registered for
(aka no entry in registration table)

It would seem to be a syntax problem. Evidently MySQL accepts a
backquote as a fieldname delimiter; Access doesn't, and prefers square
brackets. Try

SELECT [sessions].[sessionid],[SessionName], [cost], [date]
FROM [sessions] LEFT JOIN [registration] ON
[sessions].[sessionid]=[registration].[sessionid] AND
[sessions].[sid]= [registration].[scoutid]
WHERE scoutid IS NULL ;

Doublecheck my logic on the sid/scoutid join - is that in fact part of
the Primary Key, and do you in fact need a two-field join?


John W. Vinson[MVP]

 

[Eric Dropps]

Sorry, must not have expressed it clear enough. I understand the brackets
(I accidentally posted the MySQL version vs. the Access one with brackets).
Anyways, the newsgroup also butchered my table diagrams, so that does not
help.. Let's try this again:


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.

Registration
----------------
ScoutID {PK,FK1} <-- Not joined in this query
SessionID {PK,FK2} <-- Joined to sessions
Other Fields

Sessions
----------------
SessionID{PK} <-- Joined to registration
Name
Date
Cost


Thanks!

 

[John Vinson]

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]

 

[Eric Dropps]

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?

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]

 

[John Vinson]

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

My error. You need to alias the Subquery:

SELECT [sessions].[sessionid],[SessionName], [cost], [date]
FROM [sessions]
LEFT JOIN (SELECT SessionID FROM [registration]
WHERE [ScoutID] = [sid]) AS SidScout
ON [sessions].[sessionid]=[SidScout].[sessionid]
WHERE SidScout.sessionid IS NULL ;

John W. Vinson[MVP]

 

[Eric Dropps]

Thank you much!

Worked Great

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

My error. You need to alias the Subquery:

SELECT [sessions].[sessionid],[SessionName], [cost], [date]
FROM [sessions]
LEFT JOIN (SELECT SessionID FROM [registration]
WHERE [ScoutID] = [sid]) AS SidScout
ON [sessions].[sessionid]=[SidScout].[sessionid]
WHERE SidScout.sessionid IS NULL ;

John W. Vinson[MVP]