SQL query for join table and multiple values

The following approach works if you can guarantee there are no duplicates in the Table1_Table2 table. Maybe you can start here and finesse it a bit. Note how the JOIN condition works -- putting the IN in the join condition works differently than if you put the IN condition in the WHERE clause.

I've used hash marks for values that you would need to have your code insert into the SQL.

SELECT Table1.id, COUNT(Table1_Table2.Table2_id)FROM Table1JOIN Table1_Table2 ON (Table1_Table2.Table1_id = Table1.id                   AND Table1_Table2.Table2_id IN (#somelist#))GROUP BY Table1.idHAVING COUNT(Table1_Table2.Table2_id) = (#length of somelist#)

Oops -- you've changed your question in the way I suggested and I've ignored your edit. But this should get you started, as it returns all the Table1 id's that you are interested in.

Here's my solution. It's not very generalized. You should post a comment if this solution doesn't work because it is too specific to your situation.

SELECT DISTINCT Table1.idFROM Table1INNER JOIN table1_table2 a ON (table1.id = table1_table2.table1_id                           AND table2.id = 1)INNER JOIN table1_table2 b ON (table1.id = table1_table2.table1_id                           AND table2.id = 2)

If I were doing this in SQL Server I would put the values you want to check into a temp table (or table variable) and then use a variation of what @ChrisCunningham said.

CREATE TABLE #temp (Id INT)INSERT INTO #temp VALUES (1, 3, 7)SELECT a.id AS table1ID, table2.dataFROM(       SELECT Table1.id, Table1_Table2.table2_id   FROM Table1 JOIN Table1_Table2          ON (Table1_Table2.Table1_id = Table1.id                      AND Table1_Table2.Table2_id IN (SELECT Id FROM #temp))   GROUP BY Table1.id   HAVING COUNT(Table1_Table2.Table2_id) = (SELECT count (*) FROM #temp)       ) aJOIN Table2 ON Table2.id = a.Table2_id 

Of course I'm not sure what mechanisms Postgre has for temp tables, but you would even make it a stored proc where you use some sort of split function to create the values for the temp table rather that the way I did it. But at least this might give you an idea.