I am having issue getting all of the names of people and whether they play rugby or not.
If they play then display
No. At the moment I am just getting a single result of a person who plays rugby not others who did not. Can anyone help me?
select P1.name, case when S.sport = 'rugby' then 'Yes' else 'No' end as rugby from Persons P1, Persons P2, SportTogether S where P1.id = S.personA_id and P2.id = S.personB_id and S.sport = 'rugby' group by case when S.sport = 'rugby' then 'Yes' else 'No' end;
In my opinion you are using a wrong approach. You want to select persons, so select from the persons table. You want to know whether a person plays rugby, so look up persons in the rugby table. Lookups can be done with
select name, case when exists ( select null from sporttogether s where s.sport = 'rugby' and p.id in (s.persona_id, s.personb_id) ) then 'Yes' else 'No' end as plays_rugby from persons p order by name;