How select Documents which contain more than 5 words

cy163 · #1 (**permalink**) 12-03-08, 07:52

Hello ALL,

I have a table contains words extracted from a few documents.

Code:

DocID         Word     Tag
1                Book     noun
1                Pen       noun
1                Pencil    noun

2                There     prep
2                Hand     noun
2                Have     verb

3                Oil         noun
3                Thorough    adv
3                Drive      verb

4                Red        adj
4                Blue        adj
4                Pike        adj

I would like to select those (noun or verb) records in the documents which contains more than or equal to two nouns or verbs. Actually I want the following result

Code:

DocID         Word     Tag
1                Book     noun
1                Pen       noun
1                Pencil    noun

2                Hand     noun
2                Have     verb

3                Oil         noun
3                Drive      verb

How to do this with a SQL query. Thanks

r937 · #2 (**permalink**) 12-03-08, 08:03

Code:

SELECT t.DocID
     , t.Word
     , t.Tag
  FROM daTable AS t
INNER
  JOIN ( SELECT DocID
           FROM daTable
          WHERE Tag IN ( 'noun','verb' )
         GROUP
             BY DocID
         HAVING COUNT(*) >1 ) AS d
    ON d.DocID = t.DocID
 WHERE t.Tag IN ( 'noun','verb' )

cy163 · #3 (**permalink**) 12-03-08, 09:15

r937 thank you very much in deed. You always provide instant and helpful solutions.

Internet Services

Web Development

Digital Marketing

Consumer Tech