Select Items that has one item but not the other

Question

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.

Answer 1

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

Answer 2

It sounds like you want the following. Join the tables, and filter. The group by columns should include all columns from table1

Select t1.* from table1 as t1
Join table3 t3 on t1.id = t3.table1id
Join table2 t2 on t2.id = t3. Table2id
Where t2.type = 'two'
Group by t1.{column 1}, t1.{column 2}, t1. {Column n...}