CODEWITHSUNDEEP
mysqlsqlcountsubqueryrelational-division
emilyroberts2298
emilyroberts2298

Reputation: 35

Return Players With Ability To Play All Same Positions

I want to be able to support searching for a player

Let us say "Babe Ruth". I search for that player's name.

I want to return a query of only players that played ALL the same positions as babe ruth along with their batting average and homerun_count. So, Babe Ruth played outfield and pitcher, so we want players that played both positions.

Players(id, name, batting_avg, homerun_count, rookie_season, retired_season)

Positions(players_id, position)

PLAYERS Table
id  | name       | batting_avg | homerun_count | rookie_season | retired_season
1   | Babe Ruth  |   .342      |    714        |     1914      |    1935
2   | Travis Wood|   .143      |      1        |      2010     |    2020
3   | Barry Bonds|   .298      |     762       |      1986     |    2007

Positions
players_id | position
    1      | Pitcher
    1      | Outfield
    2      | Pitcher
    2      | Outfield
    3      | Outfield

So we would return Travis Wood as:

name         | id | batting_average | homerun_count
Travis Wood  | 2  |     .143        |      1

Upvotes: 1

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270823

This method aggregates all the positions, so you can get an exact match on them:

with po as (
      select po.players_id, group_concat(position order by position) as positions
      from positions po 
      group by po.players_id
     ) 
select p.* 
from players p join
     po
     on po.players_id = p.id join
     (select po2.*
      from po po2 
      where po2.players_id = (select p2.id from players p2 where p2.name = 'Babe Ruth')
     ) p_br
     on b_br.positions = po.positions;

EDIT:

If you just want all of Babe Rush's positions and some others (possibly):

with po as (
      select p.*, po.position
      from players p join
           positions po 
           on po.players_id = p.id
     ) 
select po.name, po.id, po.batting_average, po.homerun_count
from po pobr left join
     po
     on pobr.position = po.position
where pobr.name = 'Babe Ruth'
group by po.name, po.id, po.batting_average, po.homerun_count
having count(*) = count(pobr.name);

Upvotes: 0

GMB
GMB

Reputation: 222652

This is a relational division problem.

I would recommend a join to bring all positions of each player, and filtering with a exists condition on positions that Babe Ruth played at. Then, you can use a having clause to ensure that all positions were found.

select pl.*
from players pl
inner join positions po on po.player_id = pl.id
where pl.name <> 'Babe Ruth' and exists (
    select 1
    from players pl1
    inner join positions po1 on po1.player_id = pl1.id
    where pl1.name = 'Babe Ruth' and po1.position = po.Position
)
group by pl.id
having count(*) = (
    select count(*) 
    from players pl1
    inner join positions po1 on po1.player_id = pl1.id
    where pl1.name = 'Babe Ruth'
)

Demo on DB Fiddle:

id | name        | batting_avg | homerun_count | rookie_season | retired_season
-: | :---------- | ----------: | ------------: | ------------: | -------------:
 2 | Travis Wood |       0.143 |             1 |          2010 |           2020

Upvotes: 1

Related Questions