SQLQuery for Finding Neighbors-CodePudding

I have Two Tables

Towns( ID TownName SubdivisionName Country BorderCrossing ); Neighbors ( Town1 (Uses the ID from the town table) Town2 (Uses the ID from the town table) );

I have been asked to find all of the towns that physically sit on the border between two countries (namely, they are neighbors but are in different countries), independent of whether or not they include a manned border crossing. You only want each town to appear once and you want it to be recognizable to anyone (i.e.. use its commonplace identification.)

How would I accomplish this?

CodePudding user response：

Restating this in almost-English:

find towns
in the data
where exists another town
in a different country
where the two towns are neighbors

This translates trivially into SQL code:

select t1.townname
from   towns t1
where  exists
       (
         select t2.id
         from   towns t2
         where  t2.country != t1.country
           and  (t1.id, t2.id) in (select town1, town2 from neighbors)
       )
;

CodePudding user response：

Display each pair of neighbouring towns once:

Given the Neighbors table,
You can JOIN to the Towns table twice, once for each side of the Neighbors relationship;
Then filter the results to only those rows WHERE the towns are not in the same country
Then you can SELECT the townnames for each town.
To ensure that there are not duplicate results, you can make sure you order the townnames when you display then so that the LEAST name is always in the first column and the GREATEST name is always in the second column and then take the DISTINCT results.

Which gives you:

SELECT DISTINCT
       LEAST(t1.townname, t2.townname) AS townname1,
       GREATEST(t1.townname, t2.townname) AS townname2
FROM   Neighbors n
       INNER JOIN Towns T1
       ON (t1.id = n.town1)
       INNER JOIN Towns T2
       ON (t2.id = n.town2)
WHERE  t1.country <> t2.country

However, a better solution would be to ensure that there are no duplicates in the Neighbours table by using constraints:

ALTER TABLE Neighbors ADD CONSTRAINT neighbors__t1_t2__pk  PRIMARY KEY(town1, town2);
ALTER TABLE Neighbors ADD CONSTRAINT neighbors__t1_t2__chk CHECK (town1 < town2);

Then you can use a simpler query (as you will never have to worry about duplicates or having the reflexive Neighbours relationship entered into the table in the reverse direction):

SELECT t1.townname AS townname1,
       t2.townname AS townname2
FROM   Neighbors n
       INNER JOIN Towns T1
       ON (t1.id = n.town1)
       INNER JOIN Towns T2
       ON (t2.id = n.town2)
WHERE  t1.country <> t2.country

Display each town once and list its Neighbors:

Similar to the above query but ensuring that the joins to the towns take into effect that the Neighbors relationship is reflexive but that there may not be two entries for each pair of neighbours with the towns reversed then you can then JOIN in the forward and reverse direction and then GROUP BY the first town and use LISTAGG (with the DISTINCT option in later Oracle versions) to aggregate its neighbour's names:

SELECT t1.townname AS townname,
       LISTAGG(DISTINCT t2.townname, ',') WITHIN GROUP (ORDER BY t2.townname)
         AS neighbours
FROM   Neighbors n
       INNER JOIN Towns T1
       ON (t1.id IN (n.town1, n.town2))
       INNER JOIN Towns T2
       ON (t2.id IN (n.town1, n.town2) AND t1.id <> t2.id)
WHERE  t1.country <> t2.country
GROUP BY t1.townname