How to get all names for a sql table?-CodePudding

I am having issue getting all of the names of people and whether they play rugby or not.

If they play then display Yes otherwise 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?

Current SQL:

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;

CodePudding user response：

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 IN or EXISTS.

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;