Map value from table using JOIN-CodePudding

I have these 2 tables which I would like to query:

create table active_pairs
(
    pair                  text,
    exchange_id           integer
);


create table exchanges
(                        
    exchange_id         integer,
    exchange_full_name  text
);

INSERT INTO active_pairs (pair, exchange_id)

VALUES ('London/Berlin', 2),
       ('London/Berlin', 3),
       ('Paris/Berlin', 4),
       ('Paris/Berlin', 3),
       ('Oslo/Berlin', 2),
       ('Oslo/Berlin', 6),
       ('Huston/Berlin', 2);
       
INSERT INTO exchanges (exchange_id)

VALUES (2, 'Exchange 1'),
       (3, 'Exchange 2'),
       (4, 'Exchange 3'),
       (3, 'Exchange 21'),
       (2, 'Exchange 12'),
       (6, 'Exchange 11'),
       (2, 'Exchange 31');

I use this query to list all distinct pairs:

SELECT * 
   FROM common.active_pairs cp 
   GROUP BY pair, cp.exchange_id
HAVING COUNT(pair) = 1 
ORDER BY :sort

I need also to list the exchange_full_name mapped with the value exchange_id Can you advise me how I can fix this using JOIN?

CodePudding user response：

Can you explain a little more what do you need? If you need just a JOIN between the result of your query and exchanges you can use a subquery like this:

WITH pairs AS(SELECT *
   FROM active_pairs cp
   GROUP BY pair, cp.exchange_id
HAVING COUNT(pair) = 1)
SELECT p.pair, p.exchange_id, e.exchange_full_name
FROM pairs p
INNER JOIN exchanges e ON(p.exchange_id = e.exchange_id);

Fiddle