IN for many elements

I have tables:

Table Site

╔════╦═══════════════╗
║ ID ║     NAME      ║
╠════╬═══════════════╣
║  1 ║ stackoverflow ║
║  2 ║ google.com    ║
║  3 ║ yahoo.com     ║
║  4 ║ cnn.com       ║
╚════╩═══════════════╝

Table Widget

╔════╦════════════╗
║ ID ║    NAME    ║
╠════╬════════════╣
║  1 ║ polling    ║
║  2 ║ comments   ║
║  3 ║ newsletter ║
║  4 ║ mail       ║
╚════╩════════════╝

Table SiteWidget

╔═════════╦═══════════╗
║ SITE_ID ║ WIDGET_ID ║
╠═════════╬═══════════╣
║       1 ║         1 ║
║       1 ║         2 ║
║       2 ║         2 ║
║       2 ║         3 ║
║       4 ║         2 ║
║       3 ║         1 ║
║       3 ║         3 ║
║       1 ║         4 ║
║       3 ║         4 ║
║       4 ║         1 ║
║       4 ║         4 ║
╚═════════╩═══════════╝

I would like get all sites with comments (2) and mail (4).

I try:

SELECT * FROM Site 
LEFT JOIN SiteWidget ON Site.id = SiteWidget.site_id 
WHERE SiteWidget.widget_id IN (2, 4)

but this return me stackoverflow (2, 4 - OK), google.com (2 - NOT OK - without 4), yahoo.com (4 - NOT OK, without 2) and cnn.com (2, 4 - OK). How can i get all sites with 2 and 4? Always together, not singly.

Solution

This problem is called Relational Division.

SELECT  a.Name
FROM    Site a
        INNER JOIN SiteWidget b
            ON a.ID = b.Site_ID
        INNER JOIN Widget c
            ON b.Widget_ID = c.ID
WHERE   c.Name IN ('comments','mail')
GROUP   BY a.Name
HAVING  COUNT(*) = 2

SQLFiddle Demo

if uniqueness was not enforce on widget_id for every site_id, DISTINCT keyword is needed.

SELECT  a.Name
FROM    Site a
        INNER JOIN SiteWidget b
            ON a.ID = b.Site_ID
        INNER JOIN Widget c
            ON b.Widget_ID = c.ID
WHERE   c.Name IN ('comments','mail')
GROUP   BY a.Name
HAVING  COUNT(DISTINCT c.Name) = 2