Checking the existance of one record in multiple rows

Question

I have two tables which have a structure as follows:

`content` (id, foreign_id, type, date_added)
`content_territory` (id, content_id, territory_iso)

The structure of these tables is designed so that you have one content_territory record for every territory that has access to the piece of content. This means that one content item can have upwards of 200 territory records.

The problem I have is that when listing the most recent content, I would like to verify that the content has a territory IN() the users list of territories - and to ignore the record if it does not appear.

Answer 1

select id, foreign_id, type, date_added
from content
where id in (select content_id from content_territory)

Answer 2

   SELECT c.*
     FROM content AS c
LEFT JOIN content_territory AS ct
       ON c.id = ct.content_id
    WHERE ct.id IN ( ... )
 GROUP BY c.id

Answer 3

SELECT id, foreign_id, type, date_added
    FROM content
    WHERE EXISTS (SELECT *
                      FROM content_territory
                      WHERE content_id = content.id);