Simple query fails - what am I missing? 
Author Message
 Simple query fails - what am I missing?

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.



Fri, 28 Dec 2001 03:00:00 GMT
 Simple query fails - what am I missing?

What DB are you using? the SQL is different depending on this...
consider this example:
DATA:
SpeakerKey TopicrKey
1 4
1 5
1 6
2 7
2 8
2 9
3 10
3 11
3 12

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



Fri, 28 Dec 2001 03:00:00 GMT
 Simple query fails - what am I missing?
Using the IN clause in this way will return those records that have
TopicrKey 8 *or* 6 *or* 10, while the original question was to retrieve the
records with TopicrKey 8 *and* 6 *and* 10 ...
The only way I know is to use subqueries, like

    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 ...

Quote:
>What DB are you using? the SQL is different depending on this...
>consider this example:
>DATA:
>SpeakerKey TopicrKey
>1 4
>1 5
>1 6
>2 7
>2 8
>2 9
>3 10
>3 11
>3 12

>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



Sun, 30 Dec 2001 03:00:00 GMT
 Simple query fails - what am I missing?
Try this:

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

Hope it help

Lucio Fassio


Quote:

>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.



Tue, 01 Jan 2002 03:00:00 GMT
 Simple query fails - what am I missing?
As you can see from the responses, this _isn't_ a simple query! <g>

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)

Quote:

> 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.

--

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



Tue, 01 Jan 2002 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Simple set variable, what am I missing

2. Embarassingly simple SQL question - what am I missing?!

3. Help with SQL Query - what am I missing?

4. Query Analyzer text save - am I missing something?

5. badly failed optimisation on simple query

6. Simple query fails on 2 million record database

7. Simple delete query fails

8. Simple update query failing(bug maybe?)

9. simple query fails on *.DBF table

10. Simple Delphi Query Fails

11. Simple Query works in Access but fails in ASP script

12. INITIALIZE DEVICE- -What am I missing???


 
Powered by phpBB® Forum Software