Postgres Many to many mapping sql query

Question

Postgresql Database

Table User
-----------
ID | Name

1  | John
2  | Bob
3  | Sarah


Table Photo
-------------
ID | Caption

1   | Vacation
2   | Birthday
3   | Christmas

Table Comment
--------------
ID | User ID | Photo ID| Text

1  |    1    |    1    | Mexico Looks Great
2  |    2    |    1    | Sure Does
3  |    3    |    1    | Too Hot
4  |    1    |    2    | Look at that cake
5  |    3    |    2    | No ice cream?
6  |    1    |    3    | So Happy

Desire: I want to get all the photos that ONLY John(1) and Sara(3) commented on.

How do I build a SQL query that looks for photos that only have comments from user #1 and user #3, I want to EXCLUDE results where more(or less) than those two commented on.

Answer 1

The clearest and most readable way, is the Photos containing comments by:

User1 Intersect User2 Except Any other user

This SQL Fiddle and query will return that:

SELECT *
FROM Photo
WHERE ID IN (
    SELECT "Photo ID" FROM Comment WHERE "User ID" = 1
    INTERSECT
    SELECT "Photo ID" FROM Comment WHERE "User ID" = 3
    EXCEPT
    SELECT "Photo ID" FROM Comment WHERE "User ID" NOT IN (1, 3)
)