I have these 2 tables which I would like to query for a single unique record:

create table active_pairs
(
    id                    integer,
    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, exchange_full_name)

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

 

Query to list items with only one pair record:

SELECT *  FROM active_pairs ap
    INNER JOIN exchanges ce on ap.exchange_id = ce.exchange_id
WHERE ap.exchange_id = :exchangeId
GROUP BY pair, ap.exchange_id, ce.exchange_id, ap.id
HAVING COUNT(ap.pair) = 1
ORDER BY :sort
LIMIT :limit
OFFSET :offset

When I run the query I don't get proper result. I Need to get only Huston/Berlin because this is unique record(NOTE we have another record with exchange_id = 2). Now I get into result Huston/Berlin and 'London/Berlin' with exchange_id = 2 which is not correct.

Another example: When I make query for exchange_id=4 I need to get empty result because as you can see I have Paris/Berlin for exchange_id 3 and 4.

Can you advice how I can fix this issue?

CodePudding user response：

Without any more samples to check the results, the solution can be something like this:

SELECT ap.pair, ap.exchange_id, ce.exchange_id, ap.id  FROM active_pairs ap
    INNER JOIN exchanges ce on ap.exchange_id = ce.exchange_id
    INNER JOIN (SELECT pair FROM active_pairs GROUP BY pair HAVING COUNT(pair) = 1) p on p.pair = ap.pair
WHERE ap.exchange_id = :exchangeId
GROUP BY pair, ap.exchange_id, ce.exchange_id, ap.id
ORDER BY :sort
LIMIT :limit
OFFSET :offset

I guess you only want unique active pair names from the small example you gave.

CodePudding user response：

IF I understand correctly , here is what you want to do :

select * from (
   select *, count(*) over (partition by pair) as cc from active_pairs 
   ) t join exchanges e on t.exchange_id = e.exchange_id and t.cc=1

db<>fiddle here

CodePudding user response：

SQL

SELECT ap.*, ce.* FROM active_pairs ap
INNER JOIN
  (SELECT pair
   FROM active_pairs
   GROUP BY pair
   HAVING COUNT(*) = 1) subq
  ON ap.pair = subq.pair
INNER JOIN exchanges ce
  ON ap.exchange_id = ce.exchange_id
WHERE ap.exchange_id = :exchangeId
ORDER BY :sort
LIMIT :limit
OFFSET :offset;

Explanation

The subquery (subq) filters to only include pair names that appear once. This is then joined back to the active_pairs table to get the exchange_id and then joined to the exchanges table as per your original query.

Demo

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b3e2dcc5d09401e5bed1f42fdde82a6b