Help with query

Question

I have 3 tables:

Words
WordLists
WordsInLists

I need to make a query to select other words (distinct) that appear on the same list of a given word, so what I did was something like

Select the top 50 lists where word XXX appears, put into a cursor and loop the cursor to list all the words that belong to the same list where word XXX is, this works fine, but the problem is that I obviously get different sets of results (one for each list in the cursor), how can I make to put all the results in the same resultset?, is this possible without creating a temp table?

Thanks

Answer 1

I'm not sure that I completely understand the relationship between tables or what you're looking for ... but here is the SQL based on my interpretation of your question:

SELECT DISTINCT
       wil2.list
       , wil2.word
FROM   wordsinlists AS wil2
       , (SELECT wl.list
       FROM  words AS w
             , wordlists AS wl
             , wordsinlists AS wil
       WHERE w.word = wil.word
       AND   wl.list = wil.list
       AND   w.word = 'XXX') AS subquery
WHERE subquery.list = wil2.list
AND   wil2.word != 'XXX';

This will give you all lists and associated words in the lists containing 'XXX'.  If you just want the words, delete wil2.list.

Hope this helps,
Josh

Answer 2

This can be done in a single query. It would help if you post some sample schema and data using CREATE TABLE & INSERT statements. And the expected results.

Answer 3

I ended up doing nested selects using the IN directive and everything works fine.

Thanks!