SQL looking for duplicates in different columns

user_15126136 0
Viewed 42

The table I'm working with looks like this

id_psg town_from town_to
1 Rostov Moscow
1 Rostov Paris
2 Rostov Moscow
2 Singapore London
2 Singapore London
3 London Singapore
3 London Singapore
4 Singapore London
4 London Singapore
4 Singapore London
5 London Singapore
5 London Singapore
5 Paris London

We have data of people differentiated by their passanger IDs, from which town to which they travelled.

At first I was looking to find passenger IDs who have travelled from only 1 location to only 1 destination (no matter how many times they did that). So I grouped the table by id_psg and looked for duplicates. This code achieved that goal.

SELECT id_psg
FROM passengers
GROUP BY id_psg
HAVING COUNT(DISTINCT town_from) = 1 AND COUNT(DISTINCT town_to) = 1

The result I got was

|id_psg|
|------|  
|3     |

Now I'm trying to get a result of passenger IDs of passengers who have travelled only between 2 towns, not necessarily one way, but also possibly back and forth. The result would have to look like this.

|id_psg|
|------|  
|3     |  
|4     |

Because passenger with the ID 4 has travelled only from Singapore to London or from London to Singapore.
This task requires me to search for duplicates between 2 columns instead of 1 and that's what I can't solve.

sql-server sql
2 Answers

You may try:

SELECT id_psg
FROM passengers
GROUP BY id_psg
HAVING (
        MIN(town_from) = MIN(town_to) AND
        MAX(town_from) = MAX(town_to) AND
        COUNT(DISTINCT town_from) = 2 AND
        COUNT(DISTINCT town_to) = 2 )
    OR (
        COUNT(DISTINCT town_from) = 1 AND
        COUNT(DISTINCT town_to) = 1
    );

The MIN/MAX assertions ensure that the same pair of towns appears in both town_from and town_to. The distinct count assertions ensure that the passenger travelled in both directions at least one time. Or, your current criteria for one way travel can be used.

user_18632290

You can unpivot the data and aggregate:

select id_psg
from ((select id_psg, town_from as town
       from passengers
      ) union all
      (select id_psg, town_to as town
       from passengers
      )
     ) p
group by id_psg
having count(distinct town) = 2;

Note that this easily generalizes to more towns.

Here is a db<>fiddle.

user_11440350
Related