RE: SQL question

[Devraj Bhanot]

I tested this on SQL Server and it's working, I hope it should work also
with mySQL. The only change I made is use of DISTINCT

SELECT distinct(DOC_ID)
FROM tablename 
WHERE WORD IN ('word1', 'word2', 'word3')
GROUP BY DOC_ID, POS
HAVING count(*)=3;


> ----------
> From: 	Alexander I. Barkov[SMTP:bar@stripped]
> Sent: 	Thursday, May 20, 1999 11:26 PM
> To: 	mysql@stripped
> Subject: 	SQL question
> 
> Hi! 
> 
> I have table with three fields: 
> 
> char WORD, 
> int  POS, 
> int  DOC_ID.
> 
> 
> I want to write a query to find all of the unique DOC_ID that have all
> 
> of the several WORDs at the same time on the same POS.
> 
> For example, imagine that the data is:
> 
> WORD  POS  DOC_ID
> ------------------
> word1   1       1
> word2   1       1
> word3   1       1
> word1   2       1
> word2   2       1
> word3   2       1
> word1   1       2
> word2   1       2
> word3   1       2
> word1  10      20
> word2  20      30
> word3  30      40
> word4  100     200
> word5  120     300
> word6  130     400
> 
> I want to find all of the unique DOC_IDs that have all of the 
> words 'word1','word2','word3' on the same POS.
> 
> Here are the only DOC_ID 1 and 2 have all of the words at the same
> POS. 
> And I want the query to return this result:
> 
> DOC_ID
> ------
>      1
>      2
> 
> The problem is that DOC_ID 1 has all of the words on two different
> POS.
> 
> The query
> 
> SELECT DOC_ID, POS
> FROM table_name 
> WHERE WORD IN ('word1','word2','word3')
> GROUP BY DOC_ID,POS
> HAVING count(*)=3
> 
> will return  this result:
> 
> DOC_ID  POS
> -----------
> 1         1
> 1         2
> 2         1
> 
> DOC_ID 1 appeares two times, but I need it only once.
> 
> I think it will be very easy to do it with sub-selects, but ...
> 
> Could somebody help me please?
> 
> -- 
> Alexander Barkov
> JV "IZHCOM", Izhevsk,
> email:    bar@stripped      | http://www.izhcom.ru
> Phone:    [phone] | Fax: [phone]
> ICQ:      7748759
>