Consider the following table structure:
create table usuario (
id int primary key auto_increment,
nome varchar(50),
email varchar(50),
senha varchar(50)
);
create table relacionamento (
de int not null,
para int not null,
estado boolean,
primary key (de, para),
foreign key (de) references usuario(id),
foreign key (para) references usuario(id)
);
They are basically the same as yours, except that the names of some fields are slightly different, and in the relationship table, there is no primary auto-increment key (I think it's best for the de and para this function together).
Then entering the data you showed in the question:
insert into usuario (nome, email, senha)
values ('jose', 'email', 'senha'), ('gato', 'email', 'senha'),
('girafão', 'email', 'senha');
insert into relacionamento (de, para, estado) values (1, 2, true), (2, 3, false);
To solve your problem simply use the following sql query:
select * from usuario WHERE usuario.id NOT IN (SELECT de FROM
relacionamento where para = ID_USUARIO_ENTROU)
AND usuario.id NOT IN (SELECT relacionamento.para
FROM relacionamento where relacionamento.de = ID_USUARIO_ENTROU)
AND usuario.id != ID_USUARIO_ENTROU;
Basically, all records in the usuario table are returned, except those that are different from the to and from the relationship table, when the to or to are the same as the user who entered. Of course, if the id of the user is different from the one that logged in.
The following queries demonstrate the following statement:
I'm confused with the database with this! In this example,
I entered with the user number 1 (jose) so it should appear to me (3)
giraffe because the (2) cat is already my friend
select * from usuario WHERE usuario.id NOT IN (SELECT de
FROM relacionamento where para = 1)
AND usuario.id NOT IN (SELECT relacionamento.para
FROM relacionamento where relacionamento.de = 1)
AND usuario.id != 1;
Generate as output:
+---+----------+------+-------+
|id | nome | email | senha |
+-----------------------------+
|3 | girafão | email | senha |
+---+---------+-------+-------+
It is if I logged in with the user (2) cat in the non users table
no one should appear, because (1) Jose is already my friend and (3)
giraffe I already sent a friend request so there is no need
to appear.
select * from usuario WHERE usuario.id NOT IN (SELECT de
FROM relacionamento where para = 2)
AND usuario.id NOT IN (SELECT relacionamento.para
FROM relacionamento where relacionamento.de = 2)
AND usuario.id != 2;
Generate as output:
+---+----------+------+-------+
|id | nome | email | senha |
+-----------------------------+
| | | | |
+---+---------+-------+-------+
And if you log in with user 3:
select * from usuario WHERE usuario.id NOT IN (SELECT de
FROM relacionamento where para = 3)
AND usuario.id NOT IN (SELECT relacionamento.para
FROM relacionamento where relacionamento.de = 3)
AND usuario.id != 3;
Generate as output:
+---+----------+------+-------+
|id | nome | email | senha |
+-----------------------------+
|1 | jose | email | senha |
+---+---------+-------+-------+
Basically the three examples use subqueries . You can test on sqlFiddle
