How to get all names for a sql table?

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, case when = 'rugby' then 'Yes' else 'No' end as rugby
    from Persons P1, 
         Persons  P2, 
         SportTogether S
    where = S.personA_id 
      and = S.personB_id
      and = 'rugby'
    group by case when = 'rugby' then 'Yes' else 'No' end;

>Solution :

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.

  case when exists 
    select null 
    from sporttogether s
    where = 'rugby'
    and in (s.persona_id, s.personb_id)
  ) then 'Yes' else 'No' end as plays_rugby
from persons p
order by name;

Leave a Reply