Member Avatar for cy163

Hello ALL,

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

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

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

  • 2 Contributors
  • 1 Reply
  • 102 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by blocblue
Member Avatar for blocblue

I'm not sure if you've solved your problem yet... but please find below something I came up with...

Note: I've assumed you have a document table, and a documentTag table.

SELECT * 
FROM `document` AS `t1`
JOIN (
[INDENT]SELECT COUNT(*) AS `tagsMatched`, `DocId`
FROM (
[INDENT]SELECT `docId` FROM `docTags` WHERE `Tag` IN ('noun', 'verb')[/INDENT]
) WHERE `tagsMatched` >= '2' GROUP BY `docId`
[/INDENT]) AS `t2`
ON `t1`.`id` = `t2`.`DocId`

Let me know if this works :)

R.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.