CODEWITHSUNDEEP
postgresqlselectdistinctpostgresql-9.2
emilio
emilio

Reputation: 314

Selecting distinct values based on relationship

Context:

Given a table that stores user relationship information, a single record represents a unidirectional relationship i.e. user_1 is following user_2 when in the record column 'userid' is user_1 and column 'relateduserid' is user_2. If a second record exists where user column 'userid' is user_2 and column 'relateduserid' is user_1 then we consider these two 'friends' and the relationship is (logically) considered bidirectional.

EDIT: All user information is stored in a separate "user" table.

Question:

I am looking to get all distinct userid's of unidirectional relationships (i.e. there's only one record where my userid and the person I am following's userid appear) where the relateduserid is NOT a given userid.

I have attempted several queries that don't return the correct results e.g. using distinct userid returns all the userids in the table. 

select distinct userid from userRelTable where relateduserid <> 1234

Upvotes: 2

Views: 208

Answers (2)

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61696

This query will select the unidirectional relationships, filtering out the bidirectional:

SELECT userid, relateduserid FROM userRelTable
 EXCEPT ALL
SELECT relateduserid, userid FROM userRelTable;

Using this as a baseline, you may pre-filter or post-filter the results depending on the case.

For instance, to get all the distinct userid that are in a unidirectional relationship, except those who follow or are followed by userid 1234, this should work:

  SELECT distinct u.userid FROM
    (
      SELECT userid, relateduserid FROM userRelTable
        EXCEPT ALL
      SELECT relateduserid, userid FROM userRelTable
    ) u
    WHERE NOT EXISTS (
      SELECT 1 from userRelTable r
           WHERE (r.userid=u.userid AND r.relateduserid=1234)
              OR (r.userid=1234 AND u.userid=r.relateduserid)
      );

Upvotes: 1

jediCouncilor
jediCouncilor

Reputation: 828

Given the two tables user and userRelTable, I populated the user table with six users, and I populated the userRelTable with six relationships.

ID  userid
-----------
1   user_1
2   user_2
3   user_3
4   user_4
5   user_5
6   user_6

ID  userid  relateduserid
-------------------------
1   user_1  user_2
2   user_2  user_1
3   user_2  user_3
4   user_3  user_1
5   user_4  user_6
6   user_5  user_6

Given the criteria, this query will give you all users that have neither a uni-directional or bi-directional relationship with 'user_2'

    select * from [user]
    where [user].userid
    not in
    (
        select userid from userRelTable
        where userRelTable.userid = 'user_2'
        or userRelTable.relateduserid = 'user_2'
    )
    and [user].userid 
    not in
    (
        select relateduserid from userRelTable
        where userRelTable.userid = 'user_2'
        or userRelTable.relateduserid = 'user_2'
    )

The result is the following table:

ID  userid
-----------
4   user_4
5   user_5
6   user_6

The only one in question is user_3. By definition, user_3 should not be in the result set because there is a uni-directional relationship with user_2 at ID #3.

Upvotes: 1

Related Questions