SQL question with playing with mixed people database

Question

I need to write a query that returns the name of all male persons that play soccer with a female player.

The tabel is:

Persons (id, name, address, age, eyeColor, gender)

SportTogether (id, sport, personA_id, personB_id)

--The semantics of the Sport table is that personA does sport with personB.

This is which query I got till now:

select distinct P1.name
from Persons P1, Persons P2, SportTogether S1, SportTogether S2
where P1.id = S1.personA_id and P1.gender = 'male'
and   P2.id = S2.personB_id and P2.gender = 'female'

The results is two persons, which one of them is an error. I don't know how I now can get to the only one resulting person

Answer 1

This query uses the table sportstogether to join person m (for male) and person f (for female) together joining on personA or personB. As we specify the person m is male and person f is female only rows is s containing a male and a female can be used in the join. We don't need to specify that the genders are different, it is automatic.

create table persons(id int not null primary key auto_increment,name varchar(10), gender varchar(6));
create table sporttogether(personA_id int, personB_id int);
insert into persons(gender,name) values
('male','Tom'),('male','Dick'),('male','Harry'),
('female','Joan'),('female','Edna'),('female','Kate');
insert into sporttogether values(1,2),(2,3),(3,4),(4,5),(5,6),(1,6);

select
  m.name,
  f.name
from
persons m
join sporttogether s 
on m.id = s.personA_id or m.id = s.personB_id 
join persons f 
on f.id = s.personA_id or f.id = s.personB_id 
where m.gender = 'male'
and f.gender = 'female';

name  | name
:---- | :---
Harry | Joan
Tom   | Kate

db<>fiddle here