how to compare and extract 2 columns PostgreSQL

hsnevp 0
Viewed 24

I want to extract colums that different in fruit colum but have a same value in fruit_id colum. how's the query? please help

fruit fruit_id
apple a1
banana b1
citrus c1
manggo a1
orange c1

I expected the result is :

fruit fruit_id
apple a1
manggo a1
citrus c1
orange c1
sql postgresql
1 Answers

We can use exists logic here:

SELECT f1.fruit, f1.fruit_id
FROM fruits f1
WHERE EXISTS (
    SELECT 1
    FROM fruits f2
    WHERE f2.fruit_id = f1.fruit_id AND
          f2.fruit <> f1.fruit
);
Tim Biegeleisen0
Related