How to do sql select to join only unique rows?-CodePudding

I have three tables (animal, ability and can) on postgresql and need to select only UNIQUE values for the left join. How to do it?

Query #1

select * from animal;

id	name
1	dog
2	bird
3	fish

Query #2

select * from ability;

id	name
1	breathe below the surface
2	fly
3	swim
4	bark
5	see

**Query #3 (mapping table) - all abilities **

select
    animal.name as animal,
    ability.name as can,
    animal.id as animal_id,
    ability.id as ability_id
from can
inner join animal on animal.id = can.animal_id
inner join ability on ability.id = can.ability_id;

animal	can	animal_id	ability_id
dog	swim	1	3
dog	bark	1	4
dog	see	1	5
bird	fly	2	2
bird	see	2	5
fish	breathe below the surface	3	1
fish	swim	3	3
fish	see	3	5

REQUESTED RESULT - ONLY UNIQUE ABILITIES

animal	can
dog	bark
bird	fly
fish	breathe below the surface

View on DB Fiddle

CodePudding user response：

select
    animal.name as animal,
    ability.name as can
from can
inner join animal on animal.id = can.animal_id
inner join ability on ability.id = can.ability_id
where not exists (select 1 from can c where c.animal_id <> can.animal_id and c.ability_id = can.ability_id) 
;