How to select double contacts from a table

Question

I have 2 tables :

• Users [id, name, etc ...]

• Friends [id, idUsuario, idAmigoI]

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

I wanted to do two functions (or just a big 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 made the select of all that only one had (A friend of B and B not friend of A)

Answer 1

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 in the table Amigos the idUsuario and the 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 make a few 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 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 your 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 selectis:

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 do not have Postgre installed to check, but I did a test on sqlite and it worked perfectly.

I hope it helped!