CODEWITHSUNDEEP
sqloracle-database
brianjob
brianjob

Reputation: 360

omitting tuples based on conditions in other tuples

I have a table in my database containing records of friendships. There are two columns in the table: user1_id, and user2_id, indicating that user1 is friends with user2. It is set up so user1_id is always < user2_id. I'm trying to write a query to return pairs of users sorted by number of mutual friends. However, I wish to only include pairs that are not friends themselves in the results. Here's my query so far:

SELECT af.1, bf.1, count(*) as count
FROM (SELECT *
      FROM friends
      UNION
      SELECT user2_id, user1_id
      FROM friends ) af,
     (SELECT *
      FROM friends
      UNION
      SELECT user2_id, user1_id
      FROM friends ) bf
WHERE af.1 != bf.1 
AND af.2 = bf.2
GROUP BY af.1, bf.1
ORDER BY count desc

This should give me all combination of users sorted by those with the most mutual friends. However, it does not omit tuples containing pairs that are actually friends themselves. I can do it in java, but I would prefer to do it on the database side. Is there any good way to do this?

Upvotes: 1

Views: 70

Answers (2)

Mike McAllister
Mike McAllister

Reputation: 1549

You may also want to consider using the CONNECT BY clause, although that may be overkill.

Upvotes: 0

ruakh
ruakh

Reputation: 183494

SELECT af.1, bf.1, count(*) as count
FROM (SELECT *
      FROM friends
      UNION
      SELECT user2_id, user1_id
      FROM friends ) af,
     (SELECT *
      FROM friends
      UNION
      SELECT user2_id, user1_id
      FROM friends ) bf
WHERE af.1 != bf.1 
AND af.2 = bf.2
AND NOT EXISTS (SELECT 1
                FROM friends
                WHERE user1_id = af.1 AND user2_id = bf.1
                OR user1_id = bf.1 AND user2_id = af.1)
GROUP BY af.1, bf.1
ORDER BY count desc

Upvotes: 2

Related Questions