CODEWITHSUNDEEP
mysqlmariadb
stasisOo
stasisOo

Reputation: 133

MariaDB finding a soccer player that participated in all teams

i'm trying to make a query in MariaDB that returns me the name of the player that played in all teams. I'm kind of new to queries, and havent had luck using inner join (mostly duing to not understanding it very well), and all the tries with IN didnt work out that well, ideias?

Edit: I'm not at my computer at the moment, so i don't have a specific example on the code but it's like

SELECT Soccer.player
FROM Soccer
WHERE Soccer.player in (SELECT * FROM Teams, TeamPlayers
WHERE Teams.tid = TeamPlayers.tid);

Upvotes: 0

Views: 58

Answers (2)

zedfoxus
zedfoxus

Reputation: 37119

You can do something like this:

Example data

create table soccer (player varchar(100));
insert into soccer values ('john'), ('matt'), ('katie');

create table teams (teamname varchar(100));
insert into teams values ('A'), ('B'), ('C');

create table teamplayers (team varchar(100), player varchar(100));
insert into teamplayers values
('A', 'katie'), ('B', 'katie'), ('C', 'katie'),
('B', 'john'), ('C', 'john'),
('C', 'matt');

Expected result

Since katie is the only player on all teams, we should print her name.

Easier query

select tp.player
from teamplayers tp
inner join teams t on t.teamname = tp.team
group by tp.player
having count(*) = (select count(*) from teams);

Explanation

  • Join teamplayers and teams
  • Group player (and find the count in having statement)
  • If count matches the count of teams, select that player

SQL Fiddle

http://sqlfiddle.com/#!9/7110b/15

Query

This query can be written in different ways. I wrote it in a way that hopefully makes sense with inline explanation

select player
from soccer s
where not exists (

  select 1
  from

  -- get all possible combinations of team and players
  (select player, teamname from soccer, teams) main

  -- combine the above with team players
  left join teamplayers tp
    on tp.team = main.teamname 
    and tp.player = main.player

  -- find those players who DO NOT end up in one or more teams
  -- and exclude those from select (see where NOT EXISTS)
  where tp.team is null
    and main.player = s.player

);

Explanation

  • To know whether a person is on all teams, let's make a matrix of each player in all teams (the scenario where everybody is on all teams)
  • Compare this scenario with teamplayers table. Those players NOT on one or more team will have NULL in tp.team field
  • Compare all players with the above list and choose the ones who do NOT appear in that list

Result

katie

SQLFiddle example

http://sqlfiddle.com/#!9/7110b/11

Upvotes: 2

stasisOo
stasisOo

Reputation: 133

SELECT DISTINCT store_type FROM stores s1
  WHERE NOT EXISTS (
    SELECT * FROM cities WHERE NOT EXISTS (
      SELECT * FROM cities_stores
       WHERE cities_stores.city = cities.city
       AND cities_stores.store_type = stores.store_type));

Found this example on a NOT EXISTS explanation site which worked out great! But thanks @zedfoxus, hope some day i do that stuff so easy

Upvotes: 1

Related Questions