Question 114130 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL query to find rows that don't have a 1 to 1 relationship between 2 columns

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 ?

Animal Name
Dog Patch
Cat Sylvester
Mouse Gerry
Mouse Gerry
Dog Rover
Tags:

2

Answers


  1. 0

    You may use 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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search