SQL query for join table and multiple values

Question

So I have three tables that are involved in my problem, 2 regular tables and a join table for a has many and belongs to many relationship. They look like this:

table1
--id
--data

table2
--id
--data

table1_table2
--table1_id
--table2_id

So, my question is how I would query (using a join) for something that would have one or more values in the table1_table2 for one item in table1. For instance:

Table 1
+----------+
|id | data |
+----------+
|1  | none |
+----------+
|4  | match|
+----------+

Table 2
+----------+
|id | data |
+----------+
|1  | one  |
+----------+
|2  | two  |
+----------+

table1_table2
+----------------------+
|table1_id | table2_id |
+----------------------+
|1         | 1         |
+----------------------+
|4         | 1         |
+----------------------+
|4         | 2         |
+----------------------+

I need a query that would match Table 1 row id 4 because it has a link via the join to both row 1 and 2 from table 2. If this is confusing please ask anything.

Maybe I was a bit unclear, I am using table1_table2 as a join not as a from. I need to make sure it matches 1 and 2 both from table 2.

Here is my query so far...

SELECT DISTINCT table1.id,
table2.data

FROM table1

LEFT JOIN table1_table2 ON table1.id = table1_table2.table1_id
LEFT JOIN table2 ON table2.id = table1_table2.table2_id

I need a where that will make that for an entry in table 1 it matches both 1 and 2 from table 2.

The output I am looking for would be:

+---------------------+
|table1.id|table2.data|
+---------------------+
|4        |one        |
+---------------------+
|4        |two        |
+---------------------+

Answer 1

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 Table1
JOIN Table1_Table2 ON (Table1_Table2.Table1_id = Table1.id
                   AND Table1_Table2.Table2_id IN (#somelist#))
GROUP BY Table1.id
HAVING 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.