PostgreSQL: : SELECT DISTINCT rows that can occur only once

Let's say we are joining two tables (A and B) by some condition using left or inner join

WITH
a(id, x) AS (VALUES
    (0, 'a'),
    (1, 'a'),
    (2, 'b')
),
b(id, y) AS (VALUES
    (10, 'a'),
    (20, 'a'),
    (30, 'b')
),
pairs AS (
    SELECT a.id AS a_id, b.id AS b_id
    FROM a LEFT JOIN b ON a.x=b.y
)
SELECT * FROM pairs; -- how to modify this query to return the expected result?

The result will be 5 rows (2 * 2 + 1 * 1) Now the hard part: id from each table can appear in the result only once.

a_id|b_id|
----|----|
   0|  10|
 ->0|  20| -- a_id=0 can be picked only once
   1|->10| -- b_id=10 can be picked only once
   1|  20|
   2|  30|

-- so the expected result is:
a_id|b_id|
----|----|
   0|  10|
   1|  20|
   2|  30|



Read more here: https://stackoverflow.com/questions/66386680/postgresql-select-distinct-rows-that-can-occur-only-once

Content Attribution

This content was originally published by Habib Mohammad at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: