problem getting what I want from a many-to-many setup

  • Thread starter Thread starter Frederick Wilson
  • Start date Start date
F

Frederick Wilson

Hello,

I have a table of people tblpersonnel and a table of events, tblEvent

I combine them through a table (jtblNameToEvent) by NameCode and EventCode.

Every name from the tblPersonnel has a record in the join table and
every event has at least one entry in there too.

What I would like to find out is if I select an event, lets say
Swimming, I want to know all the people in the personnel table that does
not have a matching record for swimming in the jtblNameToEvent.

I've tried left and right joins and can not find anything that works.

Thanks,
Fred
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

LEFT/RIGHT JOINs should work:

SELECT P.NameCode
FROM tblPersonnel P LEFT JOIN jtblNameToEvent J
ON P.NameCode = J.NameCode
WHERE J.EventCode = 'Swimming'
AND J.NameCode IS NULL

If the EventCode is numeric:

WHERE J.EventCode = 2 -- or whatever the swimming code is
AND J.NameCode IS NULL

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgAhqYechKqOuFEgEQIugACbBfJDAUkv1osTN/bOy50kc7Quu2kAn3io
QVxwJq84LalfPfha3uKT9ZMK
=lI/x
-----END PGP SIGNATURE-----
 
I'm not sure if this will work because this is a N-N relationship: the test
for Is NULL will be performed before the test for Swimming: people
associated with a least one other event won't show up.

It might be better to use the predicate Exists () instead:

Select P.NameCode from tblPersonnel P
Where Not Exists (select * from jtblNameToEvent J where J.NameCode =
P.NameCode and J.EventCode = 'Swimming')

S. L.
 
Sylvain said:
I'm not sure if this will work because this is a N-N relationship: the test
for Is NULL will be performed before the test for Swimming: people
associated with a least one other event won't show up.

It might be better to use the predicate Exists () instead:

Select P.NameCode from tblPersonnel P
Where Not Exists (select * from jtblNameToEvent J where J.NameCode =
P.NameCode and J.EventCode = 'Swimming')

S. L.
Click to expand...
Excuse my ignorance, both of you use "P" what is that for?

P.NameCode?
 
Fred said:
Excuse my ignorance, both of you use "P" what is that for?

P.NameCode?
Click to expand...


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It's an alias for the table tblPersonnel, created in the FROM clause. A
more formal declaration is:

FROM tblPersonnel As P

This makes it easier to refer to the table tblPersonnel w/o typing the
whole name. Also makes it easier to read the SQL.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgA7/oechKqOuFEgEQIPoQCfeOD1ZU2mOvaFfPq4nPHB9vlCPx4AoMnA
VtFYvYTWkhZwQekRwRCQn+7T
=dTp6
-----END PGP SIGNATURE-----
 
Excuse my ignorance, both of you use "P" what is that for?
Click to expand...

It's an alias for tblPersonnel:

SELECT ...
FROM tblPersonell P

In the query you can use P instead of typing out tblPersonnel each
time.

John W. Vinson[MVP]
 
MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

LEFT/RIGHT JOINs should work:

SELECT P.NameCode
FROM tblPersonnel P LEFT JOIN jtblNameToEvent J
ON P.NameCode = J.NameCode
WHERE J.EventCode = 'Swimming'
AND J.NameCode IS NULL

If the EventCode is numeric:

WHERE J.EventCode = 2 -- or whatever the swimming code is
AND J.NameCode IS NULL
Click to expand...
Should that From line have an "as" in it
"from tblPersonnel as P ..."
 
Back
Top