Display rows with only single record

Question

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

create table common.active_pairs
(
    pair                  text,
    exchange_id           integer
);


create table common.exchanges
(                        
    exchange_id         bigint not null
);

Table pair holds data like London/Berlin and etc.

I use this SQL query:

SELECT *
FROM common.ACTIVE_PAIRS ap
    INNER JOIN common.exchanges ON exchanges.exchange_id = ap.exchange_id
    // implement logic to check common.ACTIVE_PAIRS.pair to have only one record
WHERE ap.exchange_id = 1
ORDER BY ap.id
LIMIT 10
OFFSET 10

How I can select table rows which hold only a single distinct record which is not repeating in other table rows?

Answer 1

you want group by and having

SELECT ce.exchange_id, cp.exchange_id, pair
FROM common.active_pairs cp
JOIN common.exchanges ce on cp.exchange_id = ce.exchange_id
GROUP BY pair, cp.exchange_id, ce.exchange_id
HAVING COUNT(*) = 1
ORDER BY ce.exchange_id
LIMIT 10
OFFSET 10;