Given two columns (Animal and Name) how do I write a SQL statement that finds the rows that don't have a 1 to 1 relationship i.e. Dog = Patch and Dog = Rover ?

CodePudding user response：

Something like this would work (probably not the best way to do it):

SELECT DISTINCT Animal, Name
FROM (
  SELECT Animal, Name, COUNT(DISTINCT Name) OVER (PARTITION BY Animal) NamesPerAnimal
  FROM Table
  UNION
  SELECT Animal, Name, COUNT(DISTINCT Animal) OVER (PARTITION BY Name) AnimalsPerName
  FROM Table
)
WHERE NamesPerAnimal > 1 OR AnimalsPerName > 1

This looks the number of distinct Animal values for each Name anywhere in the table and vice versa and returns only the rows where one or the other is greater than 1.

CodePudding user response：

You may EXISTS operator with a correlated subquery as the following:

SELECT Animal, Name
FROM table_name T
WHERE EXISTS(SELECT 1 FROM table_name D WHERE D.Animal=T.Animal AND D.Name<>T.Name)
ORDER BY Animal

See a demo.