sql – How to Select Dual Contacts from a Table

Question:

I have 2 tables:

  • Users [id, name, etc…]

  • Friends [id,Userid,FriendidI]

In the Friends table, userid corresponds to the id of the user who owns the contact and idAmigo the id of the user who is in fact the contact.

I wanted to make two functions (or just a very large select) in postgresql, one that made a select with all the friendships they both had (A friend of B and B friend of A), and another that would select all of them just one had (A friend of B and B not friend of A)

Answer:

To make it easier to understand, I created the two tables with the columns informed:

CREATE TABLE Usuarios (
  id integer,
  nome varchar(20)
);

CREATE TABLE Amigos (
  id integer,
  idUsuario integer,
  idAmigo integer
);

As it was not informed, I considered that the table Amigos will always be filled in the idUsuario and idAmigo . After creating the tables, I entered some data:

  INSERT INTO Usuarios
    VALUES (1, 'A');
  INSERT INTO Usuarios
    VALUES (2, 'B');
  INSERT INTO Usuarios
    VALUES (3, 'C');
  INSERT INTO Usuarios
    VALUES (4, 'D');
  INSERT INTO Usuarios
    VALUES (5, 'E');

  INSERT INTO Amigos
    VALUES (1, 1, 2);
  INSERT INTO Amigos
    VALUES (2, 1, 3);
  INSERT INTO Amigos
    VALUES (3, 2, 1);
  INSERT INTO Amigos
    VALUES (4, 2, 3);
  INSERT INTO Amigos
    VALUES (5, 3, 1);
  INSERT INTO Amigos
    VALUES (6, 3, 2);
  INSERT INTO Amigos
    VALUES (7, 4, 1);
  INSERT INTO Amigos
    VALUES (8, 4, 3);
  INSERT INTO Amigos
    VALUES (9, 4, 5);
  INSERT INTO Amigos
    VALUES (10, 5, 4);

To return users and their friends, just do some inner joins :

SELECT
  u1.nome || ' é amigo de ' || u2.nome amizade
FROM Amigos a
INNER JOIN Usuarios u1
  ON (u1.id = a.idUsuario)
INNER JOIN Usuarios u2
  ON (u2.id = a.idAmigo);

The result of the select is:

A é amigo de B
A é amigo de C
B é amigo de A
B é amigo de C
C é amigo de A
C é amigo de B
D é amigo de A
D é amigo de C
D é amigo de E
E é amigo de D

To return those users who have friends but their friends don't have them as friends, you can do:

SELECT
  u1.nome || ' é amigo de ' || u2.nome || ', porém ' || u2.nome || ' não é amigo de ' || u1.nome amizade
FROM Amigos a
INNER JOIN Usuarios u1
  ON (u1.id = a.idUsuario)
INNER JOIN Usuarios u2
  ON (u2.id = a.idAmigo)
WHERE NOT EXISTS (SELECT 1 FROM Amigos ai WHERE ai.idUsuario = a.idAmigo AND ai.idAmigo = a.idUsuario);

The result of the select is:

D é amigo de A, porém A não é amigo de D
D é amigo de C, porém C não é amigo de D

Unfortunately I don't have Postgre installed to verify, but I did a test on sqlite and it worked perfectly.

I hope it helped you!

Scroll to Top