Select Items that has one item but not the other I have three tables:

Table1:

ID      Name
1       Item1
2       Item2
3       Item3


Table2:

ID      Name         Type
1     Logistics       One
2     Human Resource  Two
3     Finance         Three


Table3 (Many to Many Table):

ID     Table1ID      Table2ID
1         1             1
2         1             2 
3         2             1
4         3             1
5         3             2
5         3             3


I want to select all the items from table1 that exist in many to many table for just type one from table2 and not to the type two. the type three is not concerned. So all the items from table one that has a record in table3 for only type one and not two.
You can use NOT EXISTS to eliminate rows linked to undesirable types (like @a_horse already suggested).

But since multiple rows can match any row in table1 also use EXISTS to qualify rows linked to desirable types:

SELECT t1.*
FROM   table1 t1
WHERE  EXISTS (
   SELECT 1
   FROM   table2 t2
   JOIN   table3 t3 ON t3.table2_id = t2.id 
   WHERE  t2.type = 'one'
   AND    t3.table1_id = t1.id 
   )
AND    NOT EXISTS (
   SELECT 1
   FROM   table2 t2
   JOIN   table3 t3 ON t3.table2_id = t2.id 
   WHERE  t2.type = 'two'
   AND    t3.table1_id = t1.id 
   );


With joins like a_horse demonstrated, you would multiply rows and be forced to add an aggregation or DISTINCT step to get distinct rows again. Don't.

Also, you can use the same subquery twice, making the query simpler to understand and maintain.

I would see this as special case of relational-division, btw. Related:


Select rows which are not present in other table
How to filter SQL results in a has-many-through relation