Problem with SQL query

Posted on

Question :

I have the following Problem: I want as output a specific document (TITLE) that contains two different terms (TERM). For example the title of a fairy tale, that contains the names of two different characters. And I have the following data structure (bold=tables, italic=keys):

DOCUMENT ( DOCUMENT_ID, TITLE )

DOCUMENT TERM ( DOCUMENT ID->DOCUMENT, POSITION OF TOKEN IN DOCUMENT, TERM,
TOKEN, POS ID->POS )

POS ( POS ID, LOW, HIGH, DESCRIPTION, LONG DESCRIPTION, PARENT POS->POS )

I tried the following solution, but it didn’t work. I hope someone can help:

SELECT
    TITLE
FROM
    DOCUMENT
WHERE 
    DOCUMENT_ID
IN (
    SELECT
        DOCUMENT_ID
    FROM
        DOCUMENT_TERM
    WHERE
        (TERM = 'Term1') AND (TERM = 'Term2')
)

Answer :

A field can never have 2 different values. Try the INTERSECT:

SELECT TITLE
FROM   DOCUMENT
WHERE  DOCUMENT_ID IN
 (SELECT DOCUMENT_ID FROM DOCUMENT_TERM WHERE TERM = 'Term1'
  INTERSECT
  SELECT DOCUMENT_ID FROM DOCUMENT_TERM WHERE TERM = 'Term2');

Leave a Reply

Your email address will not be published. Required fields are marked *