Simple query - broken brain.....

[D. Shane Fowlkes]

I'm still stuck on that SELECT issue. I know my SELECT is "wrong" but I
can't seem to conceptualize the proper way to go about getting the proper
results.

I have a many-to-many key table. The purpose of the table is to store
relations made by two other tables. It's a very common practice. What I'm
TRYING to do is come up with a simple SELECT Statement that will give me a
list of unique ("distinct") ContactIDs where the following WHERE clauses
tests true.


SELECT ContactID, CategoryID
FROM CategoryMatches
WHERE CategoryID Not In (16,19,20,35,39)
ORDER BY ContactID



As expected, the above query doesn't return RECORDS from the table where the
CategoryID matches the NOT IN clause. However, I want the results to
exclude ALL ContactIDs where the Category is EVER found for the ContactID.
Make sense?

I know my query is "wrong" but how do I write to display what I'm trying to
achieve? If the CategoryID is ever found for the ContactID, I want it
excluded from this list. If a ContactID has ANY of the matching categoryIDs
listed above, they should NOT be in the results. If you look at the sample
query results and then the sample data from the actual table, I'm trying to
exclude ContactID 5 all together because ONE of the Categories is #16...and
so on.

If it helps to play with the real thing, I placed a sampled MS Access file
at http://www.easterassociates.com/sample.mdb I realize the query is doing
exactly what I'm telling it to. I just can't seem to connect the dots on
how to get what I'm trying to achieve.

Sample output of query:

Query1 ContactID CategoryID
2 9
3 9
4 9
5 2
5 5
5 9
5 10
5 15
5 36
6 15
7 5
7 10
8 14
8 15
9 10
9 26
10 9
11 14
11 15
12 7
12 10
12 15
13 14
14 9
15 15
16 9
17 7
17 8
17 9
17 10
17 11
17 14
17 15
17 30
18 9
19 9
20 9




Sample of Table

CategoryMatches ID ContactID CategoryID
7 2 9
8 3 9
9 4 9
22346 5 2
10 5 5
11 5 9
12 5 10
13 5 15
14 5 16
23258 5 36
15 6 15
16 6 16
17 7 5
18 7 10
19 8 14
19623 8 15
19624 8 16
20 9 10
21789 9 26
21 10 9
22 11 14
23 11 15
24 12 7
25 12 10
26 12 15
27 13 14
28 14 9

 

[Marshall Barton]

Not sure I followed all that, but maybe this is more like
what you're trying to do:

SELECT DISTINCT T.ContactID, T.CategoryID
FROM CategoryMatches AS T
WHERE Not Exists(SELECT X.CategoryID
FROM CategoryMatches AS X
WHERE T.ContactID = X.ContactID
AND X.CategoryID In (16,19,20,35,39))
ORDER BY T.ContactID

 

[John Vinson]

I'm still stuck on that SELECT issue. I know my SELECT is "wrong" but I
can't seem to conceptualize the proper way to go about getting the proper
results.

I have a many-to-many key table. The purpose of the table is to store
relations made by two other tables. It's a very common practice. What I'm
TRYING to do is come up with a simple SELECT Statement that will give me a
list of unique ("distinct") ContactIDs where the following WHERE clauses
tests true.


SELECT ContactID, CategoryID
FROM CategoryMatches
WHERE CategoryID Not In (16,19,20,35,39)
ORDER BY ContactID



As expected, the above query doesn't return RECORDS from the table where the
CategoryID matches the NOT IN clause. However, I want the results to
exclude ALL ContactIDs where the Category is EVER found for the ContactID.
Make sense?

Try the NOT EXISTS clause:

SELECT ContactID, CategoryID
FROM CategoryMatches
WHERE NOT EXISTS
(SELECT CategoryID FROM CategoryMatches AS X WHERE X.CatgoryID IN
(16,19,20,35,39));


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps