SQL only join when key matches one criteria

user_4793408 0
Viewed 56

I have two tables that look like the following:

Table 1 (IDs):

ID    
1
2
3
4
5

Table 2 (Categories):

ID    CAT
1      A
1      B
2      A
3      A
4      B
5      A
5      B
5      C

What I would like to do is join table 2 to table 1 and only keep instances where there is 1) Only one match 2) That match is CAT = A

So my final table would look like:

ID    CAT
 2     A
 3     A
sql
2 Answers

One method is aggregation and having:

select id, max(cat)
from t
group by id
having count(*) = 1 and
       max(cat) = 'A';
user_11440350

You can use a subquery with not exists:

select t1.id, t2.cat 
from table1 t1 
join table2 t2 on t1.id = t2.id 
where t2.cat = 'A' and not exists (select 1 from table2 t3 where t3.id = t1.id and t3.cat != 'A');

Output:

id cat
2 A
3 A
user_73267380
Related