Put simply,  if I have a table (named SpeakerTopics) with 2 fields:

        SpeakerKey      :       Number (long)
        TopicKey                :       Number (long)

And for example is populated thusly:

SpeakerKey      TopicKey
----------------        -------------
122                             34
122                             5
100                             9
100                             2
100                             7
115                             4

My query is:

        SELECT * FROM SpeakerTopics WHERE ((TopicrKey=34) AND (TopicKey=5))

It will return an empy recordset because no single record can contain both
values within a single field.  My question is how can I structure a query
that will give me the desired results.  

In the above example I want all SpeakerKeys that have TopicKeys of both 34
and 5.  I should get the result: 122

If I wanted TopicKeys 9,2, & 7 I would want 100 returned.

SQL:
SELECT * FROM SpeakerTopics WHERE TopicrKey in (6,8,10)

add all the possible values for TopicrKey in the () signs

RESULT:
SpeakerKey TopicrKey
1 6
2 8
3 10

    SELECT * FROM SpeakerTopics WHERE TopicrKey = 34 AND Speakerkey IN
(SELECT * FROM Speakertopics WHERE TopicrKey = 35)

Of course, if you have many conditions to fullfill, the SQL statement could
become rather complex.
Hope this helps ....

Steve Fowler heeft geschreven in bericht ...

SELECT distinct SpeakerKey
FROM SpeakerTopics
WHERE (((SpeakerTopics.TopicKey)=34)) OR
(((SpeakerTopics.TopicKey)=5));

Hope it help

Lucio Fassio

While inefficient, the following should work. What it's doing is
creating a list of SpeakerKeys where TopicKey is 34, and using that to
create a subset of SpeakerTopics in which to look for TopicKey 5

        SELECT * FROM SpeakerTopics
         WHERE TopicKey=5
           AND SpeakerKey IN (SELECT SpeakerKey FROM SpeakerTopics
                                WHERE TopicrKey=34)

Beer, Wine and Database Programming.  What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://webhome.idirect.com/~djsteele/