Not to Join tables depending on the conditions

Question

For example, I have two tables.

table a

id

1
2
3
4
5

table b

id  type
1   type1
2   type2
3   type3
4   type1
5   type1
1   type1
2   type1
5   type2

I join my tables.

select a.id, b.type from 
table a left join table b on
a.id = b. id

for id = 5 I get:

id    type 
5     type1
5     type2

I only need the id where type1

select a.id, b.type from 
table a left join table b on
a.id = b. id where b.type = 'type1'

for id = 5 I get:

id    type 
5     type1

id =5 also has type2 in table 2.

My problem is that I need to exclude the id value where there is at least one type2 value in the stat field.

For id =5 I expect the empty table. Because id =5 also hase type2

Is it possible to realize this? I tried to use exists, contains, but without result

Answer 1

We can use exists logic:

SELECT a.id, b.type
FROM tableA a
LEFT JOIN tableB b
    ON a.id = b.id
WHERE
    b.type = 'type1' AND
    NOT EXISTS (
        SELECT 1
        FROM tableB t
        WHERE t.id = b.id AND
              t.type = 'type2'
    );