Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 7 down vote accepted

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

share|improve this answer
1  
Thank you very much. That is what I was looking for. –  Aslan986 Feb 26 '13 at 13:43
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? –  DavideChicco.it Mar 22 '13 at 11:06
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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