Help with JOIN and records only in 1 side of it!

[haydnw]

*SQL at bottom of post*

Hi,

I have a table which lists people (tblContacts,
PK/FK=lngContactID), a table which lists events
(tblEvents, PK/FK=lngEventID) and a join table which lists
invitations to events (tblEventAttendance FK=lngContactID
& lngContactID).

My problem is that I'm currently designing a form where,
once one set of invitations have been sent, extra people
can be invited. To do this, I need to create a list of
people to select from, and this requires a query which
shows Contacts:

a) who have not been already invited to the event in
question (ie tblEventAttendance.lngEventID <>Forms!
frmEvent!txtlngEventID).
b) who have never been invited to an event before

a) is fine, and I can use SELECT DISTINCT to only show
each Contact once. My problem is that this doesn't satisfy
b). People who have never been invited to an event have no
records in tblEventAttendance and so the query does not
pick them up.

If I make a left join between tblContacts and
tblEventAttendance, then my results now include people
who've never been invited to any event, but also include
all the people who have already been invited to the event
in question.

What I need is some sort of way to say 'All people who've
never been invited to an event PLUS if people have been
invited to an event only include them if it's not the
current event'.

I've explained this really badly but I've re-written three
times now! I hope someone can understand and maybe point
me in the right direction.

H
(SQL below)



==================================================
Current query which picks up all people who've been
previously invited to an event, but not invited to the
event in question:

SELECT DISTINCT tblContacts.lngContactID
FROM tblContacts INNER JOIN tblEventAttendance ON
tblContacts.lngContactID = tblEventAttendance.lngContactID
WHERE (((tblEventAttendance.lngEventID)<>[Forms]!
[frmEvents]![txtlngEventID];

But this doesn't show people who've never been invited to
any events!

 

[Gary Walter]

Hi haydnw,

Would it be correct to say

I want all the records from tblContacts
that have not been invited to this event?

who have been invited

SELECT lngContactID
FROM tblEventAttendance
WHERE
lngEventID = Forms!frmEvent!txtlngEventID

I believe then that all we have to do is
LEFT JOIN tblContacts
with the above, and ask for the
records where we don't get a match.

SELECT tblContacts.lngContactID
FROM
tblContacts
LEFT JOIN
(SELECT lngContactID
FROM tblEventAttendance
WHERE
lngEventID = Forms!frmEvent!txtlngEventID) AS q
ON tblContacts.lngContactID = q.lngContactID
WHERE
q.lngContactID IS NULL;

It's Friday, plus I kind of rushed this
because I have to get to work,
but I think you might get the idea.

Good luck,

Gary Walter

 

[haydnw]

"Would it be correct to say

I want all the records from tblContacts
that have not been invited to this event?"

Yes! Yes it would! That works brilliantly, thank you so
much! I think I understand what you've done, but I'll go
away and have another look just to make sure. This is
amazing, thanks again!

H

-----Original Message-----
Hi haydnw,

Would it be correct to say

I want all the records from tblContacts
that have not been invited to this event?

who have been invited

SELECT lngContactID
FROM tblEventAttendance
WHERE
lngEventID = Forms!frmEvent!txtlngEventID

I believe then that all we have to do is
LEFT JOIN tblContacts
with the above, and ask for the
records where we don't get a match.

SELECT tblContacts.lngContactID
FROM
tblContacts
LEFT JOIN
(SELECT lngContactID
FROM tblEventAttendance
WHERE
lngEventID = Forms!frmEvent!txtlngEventID) AS q
ON tblContacts.lngContactID = q.lngContactID
WHERE
q.lngContactID IS NULL;

It's Friday, plus I kind of rushed this
because I have to get to work,
but I think you might get the idea.

Good luck,

Gary Walter

I have a table which lists people (tblContacts,
PK/FK=lngContactID), a table which lists events
(tblEvents, PK/FK=lngEventID) and a join table which lists
invitations to events (tblEventAttendance FK=lngContactID
& lngContactID).

My problem is that I'm currently designing a form where,
once one set of invitations have been sent, extra people
can be invited. To do this, I need to create a list of
people to select from, and this requires a query which
shows Contacts:

a) who have not been already invited to the event in
question (ie tblEventAttendance.lngEventID <>Forms!
frmEvent!txtlngEventID).
b) who have never been invited to an event before

a) is fine, and I can use SELECT DISTINCT to only show
each Contact once. My problem is that this doesn't satisfy
b). People who have never been invited to an event have no
records in tblEventAttendance and so the query does not
pick them up.

If I make a left join between tblContacts and
tblEventAttendance, then my results now include people
who've never been invited to any event, but also include
all the people who have already been invited to the event
in question.

What I need is some sort of way to say 'All people who've
never been invited to an event PLUS if people have been
invited to an event only include them if it's not the
current event'.

I've explained this really badly but I've re-written three
times now! I hope someone can understand and maybe point
me in the right direction.

H
(SQL below)



==================================================
Current query which picks up all people who've been
previously invited to an event, but not invited to the
event in question:

SELECT DISTINCT tblContacts.lngContactID
FROM tblContacts INNER JOIN tblEventAttendance ON
tblContacts.lngContactID = tblEventAttendance.lngContactID
WHERE (((tblEventAttendance.lngEventID)<>[Forms]!
[frmEvents]![txtlngEventID];

But this doesn't show people who've never been invited to
any events!

.