Stuck on query

[vmon]

I have three tables A, B, and C. Columns are Key1, Key1+Key2, and Key2 respectively. I want all C records that are not in B for A.Key1 = 'X' only. I am somewhat close but no cigar yet. Here is what I have so far.

SELECT C.Key2
FROM C LEFT OUTER JOIN
B ON C.Key2 = B.Key2
WHERE (NOT EXISTS
(SELECT B.Key2
FROM A INNER JOIN
B ON A.Key1 = B.Key1
WHERE C.Key2 = B.Key2 AND (A.Key1 = 'X')))

Thanks in advance,
vmon

 

[SonOfEmidec1100]

I might be missing something, but why do you need to consider table A ? I have had some similar problems that I just could not resolve without creating a view. Anyway try this it might work.

SELECT C.*
FROM C
WHERE
(Key2 NOT IN
(SELECT Key2
FROM B
WHERE Key1 = 'X'))

 

[vmon]

I am open for suggestions. I will give that a try. What I am doing is this.

I only want records from C that have not been used in B for a specific A.Key1. In other words I want to know what C records I have available that I can create a B record for. The actual data is Groups M:M Members. A many to many that is normalized by B. A member can be in several groups but a member can be in a group only once, so when I look at a group to add members I only want to see members I have not put in a group.

What do you think?

 

[SonOfEmidec1100]

Mmmm... should work assuming that 'X' is a valid key1 in table A. Open to a second opinion.