Filtering data that could have more than one category

Question

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?

Answer 1

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).

Answer 2

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.

Answer 3

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.)