Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unanswered: How select Documents which contain more than n tag-specified words

    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
    Last edited by cy163; 12-03-08 at 09:01.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    130
    r937 thank you very much in deed. You always provide instant and helpful solutions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •