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
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|||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.|||I ended up doing nested selects using the IN directive and everything works fine.
Thanks!
