Map pairs which are duplicate from 2 tables and find duplicate records-CodePudding

I have a 2 tables and one view which I want to find which pairs are duplicate:

    create table main_pairs(
       pair            text,
       id              integer
    )
    
-- view for second_pairs

create view second_pairs_view
        (pair, id) as
         SELECT (SELECT ap_1.pair
         FROM main_pairs ap_1
         WHERE ap_1.id = tp.pair) AS pair,
         tp.id
         FROM second_pairs tp
         LEFT JOIN main_pairs ap ON ap.id = tp.pair;


    create table second_pairs(
       pair         integer
    )


    INSERT INTO main_pairs (pair, id)
    
    VALUES ('London/Berlin', 1),
           ('London/Rome', 2),
           ('Paris/Berlin', 3),
           ('Paris/Budapest', 4),
           ('Oslo/Berlin', 5),
           ('Oslo/Berlin', 6),
           ('Huston/Berlin', 7);

    INSERT INTO second_pairs (pair)
    
    VALUES (6),
           (7),
           (8),
           (9),

As you can see I have a duplicate records 6 and 7. Using the view second_pairs_view how I can list the pairs which have duplicate records?

How I can check this with SQL query?

CodePudding user response：

Use INNER JOIN , it will return the matching records :

  SELECT sp.pair
  from main_pairs mp 
  inner join second_pairs sp on mp.id=sp.pair;

Demo