5

I have a table like the following:

Annotation (
    document,
    term,
    category
)

Where document and term are some ID, while category is an integer.

The couple document - term is not unique, i.e. I could have the same couple with a different category.

document_id_1, term_id_1, category_1
document_id_1, term_id_1, category_2
document_id_1, term_id_1, category_3

I would like to design a query such that it return only the couple document-term for whom there exists a only a row with category = 1.

E.g. in the previous example the couple document_id_1 - term_id_1 is not returned becouse there exist also other two rows, with different values of category.

Can you give me some hints on how to do that?

3 Answers 3

8

If I understood you correctly, one way to achieve this is getting those rows where category = 1, then check for the non-existence of non-category1 rows:

SELECT document, term
FROM Annotation ann
WHERE 
    category = 1
    AND NOT EXISTS (
        SELECT 1
        FROM Annotation a
        WHERE 
            a.document = ann.document
            AND a.term = ann.term
            AND category <> 1
    )
;

(Thanks to DavideChicco.it for showing that this was overly complicated).

1
  • 1
    @dezso Is the first "EXISTS ( SELECT .." query mandatory? I tried this: SELECT document, term FROM Annotation ann WHERE category = 1 AND NOT EXISTS ( SELECT 1 FROM Annotation a WHERE a.document = ann.document AND a.term = ann.term AND category <> 1 ). Is this correct the same? Mar 22, 2013 at 11:06
7

You should be able to use something similar to this:

select a1.document, a1.term, a1.category
from annotation a1
where category = 1
  and exists (select document, term
              from annotation a2
              where a1.document = a2.document
                and a1.term = a2.term
              group by document, term
              having count(distinct category) = 1)

See SQL Fiddle with Demo.

This will filter for the rows where the category =1 but then checks to make sure that the document and term only has one distinct row in the table.

7

If this is PostgreSQL 8.4+, you could also try the following:

WITH counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY document, term) AS category_count
  FROM Annotation
)
SELECT
  document,
  term,
  category
FROM counted
WHERE category = 1
  AND category_count = 1
;

The counted Common Table Expression returns rows supplied with row counts per every partition of (document, term). The main query only needs to filter on the condition that a row belongs to a specific category and that the corresponding row count is 1.

Note also that if your design allows duplicate entries of (document, term, category), you'll need to replace COUNT(*) with COUNT(DISTINCT category). (That may accordingly result in more than one row per (document, term) in the output.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.