Task: get all pieces of content including a certain list of keywords (e.g. ‘Air’, ‘Earth’, ‘Water’, ...)

generic-relational-query.sql

SELECT 
    `contents`.* 
FROM 
    `contents` 
    INNER JOIN `contents_keywords` `contents_keywords_0` 
        ON `contents`.`id` = `contents_keywords_0`.`content_id` 
    INNER JOIN `contents_keywords` `contents_keywords_1` 
        ON `contents`.`id` = `contents_keywords_1`.`content_id` 
    INNER JOIN `contents_keywords` `contents_keywords_2` 
        ON `contents`.`id` = `contents_keywords_2`.`content_id` 
    ...
        ...
WHERE 
  `contents_keywords_0`.`keyword_id` = 1 
  AND `contents_keywords_1`.`keyword_id` = 2 
  AND `contents_keywords_2`.`keyword_id` = 3
  ...  

# This assumes you know the keyword IDs without having to join back to the keywords table

mongodb-query.js

db.content.find( { keywords : { $all : ["Air", "Earth", "Water", ...] } } )