Hello, I want to do the following: Create in MySQL the n-to-n relationship where men have friendships with women and women also have friendships with men.
Example:
Joao has friendship with Fernanda , but Fernanda strong> but with Carlos , but João is still friends with Fernanda .
I've created an n-to-n model where men have friendship with women but can not make women befriend men and vice versa.
See my following SQL script:
create database pessoas;
use pessoas;
create table homens (id int auto_increment, nome varchar(10), primary key (id));
create table mulheres (id int auto_increment, nome varchar(10), primary key (id));
create table amizades (id int auto_increment, homem_id int, mulher_id int,
primary key(id),
foreign key(homem_id) references homens(id),
foreign key(mulher_id) references mulheres(id));
insert into homens values
(default, 'Joao'),
(default, 'Flavio'),
(default, 'Carlos');
insert into mulheres values
(default,'Ana'),
(default,'Fernanda'),
(default,'Julia');
insert into amizades values
(default, 1, 2),
(default, 2, 1),
(default, 3, 3);
/* join com 3 tabelas, aparecer o id e nome do homem e depois o id e nome da mulher de quem o homem é amigo */
select homens.id, homens.nome, mulheres.id, mulheres.nome from homens join amizades on homens.id = amizades.homem_id
join mulheres on amizades.mulher_id = mulheres.id order by homens.id;
I want an explicit relationship in the database so I know who has friendship with who and not only to simulate this relationship by changing the order of the columns in SELECT
, understood? What better way to do this?