How to select double contacts from a table

Question

How to select double contacts from a table

Navigation

#1 by (2 votes)

3

I have 2 tables:

Users [id, name, etc ...]
Friends [id, idUser, idImigo]

In the Friends table, idUser corresponds to the id of the user who has the contact and idAmigo the id of the user who is actually the contact.

I wanted to do two functions (or just a very large select) in postgresql, one that made a select with all the friends they had both (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)

sql postgresql

asked by anonymous 12.02.2017 / 01:35

1 answer

Call progress bar class from another file Layout different on other computers?

score 2 · Accepted 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 I was not informed, I considered that in the Amigos table, idUsuario and idAmigo will always be filled. After creating the tables, I inserted 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 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 do not 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 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 do not have Postgre installed to check, however I did a test in sqlite and it worked perfectly.

I hope you have helped!