PostgreSQL self-join query condition not to return repeated results

by roy naufal   Last Updated August 17, 2019 15:06 PM

I have this simplified query :

SELECT a.p_key, b.p_key
FROM test AS a, test AS b       
WHERE 
a.p_key < b.p_key -- symmetry breaker

Below is the returned data set, the * marks already 'retrieved' b.p_keys:

a.p_key p.b_key
1   2
1   3

2   3* (already linked to a.p_key = 1)
2   4

3   4* (already linked to a.p_key = 2)
3   5

I have done much research and experimentation but can't seem to reach the result I need:

I want to exclude b.p_keys that have already been retrieved previously, so my actual result set should look like this:

a.p_key p.b_key
1   2
1   3

2   4

3   5
Tags : postgresql


Related Questions


Updated April 09, 2018 20:06 PM

Updated June 06, 2017 18:06 PM

Updated May 15, 2017 19:06 PM

Updated March 15, 2017 02:06 AM