SQL Databases not getting the good result that i want-CodePudding

For this problem I need to write a query that returns the name of all male persons that play soccer with a female player. Eliminate duplicates from your result.

select distinct name
FROM Persons, SportTogether S
WHERE Persons.gender = "male"
AND (Persons.id = S.personA_id AND sport ="soccer" 
AND S.personB_id IN(SELECT id FROM Persons WHERE gender ="female"))

OR 
(Persons.id = S.personB_id AND sport ="soccer" AND S.personA_id IN 
(SELECT id FROM Persons WHERE gender ="female"))

I get now three names. 2 of them are men and are good, but I also get a third name which is a woman name and that is one is false. I have tried a lot, but I don't know where the error is. The database is:

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

SportTogether (id, sport, personA_id, personB_id)

CodePudding user response：

If I understand the SQL correctly, there should be "()" to make the Persons.gender = "male" work with the later "OR". Could you try:

select distinct name
FROM Persons, SportTogether S
WHERE Persons.gender = "male"
AND 
((Persons.id = S.personA_id AND sport ="soccer" 
AND S.personB_id IN(SELECT id FROM Persons WHERE gender ="female"))
    OR 
(Persons.id = S.personB_id AND sport ="soccer" AND S.personA_id IN 
(SELECT id FROM Persons WHERE gender ="female")))

CodePudding user response：

Join the table persons two time like

SELECT DISTINCT P1.name
FROM Persons P1 JOIN SportTogether S ON P1.id = S.personA_id 
JOIN Persons P2 ON p2.id = S.personB_id 
WHERE P1.gender = 'male' AND P2.gender = 'female' AND sport ='soccer'