0

I have a table "player" as follow where: ID is primary key.

  • date = date they play (just for 1 month, so could from 1 to 31)
  • Name = name of the players
  • Sport = sport they play and there can be many sports in the list; but i only focus on the one who play "football" and play more than 2 games in one day

This is the table "player".

+----+------------+-------+-------------+
| ID | Date       | Name  | Sport       |
+----+------------+-------+-------------+
|  1 |          1 | A     | football    |
|  2 |          1 | A     | soccer      |
|  3 |          3 | A     | tennis      |
|  4 |          2 | B     | tennis      |
|  5 |          2 | B     | football    |
|  6 |          1 | C     | basketball  |
|  7 |          1 | C     | tennis      |
|  8 |          1 | C     | fishing     |
|  9 |          4 | D     | football    |
+----+------------+-------+-------------+

I want to find list of the people (name and sport) who DO NOT:

  • practice "football" + other sport(s) in one day.

note: if someone who play "football" + other game(s) in one day, we remove him from the list for that day. only remove him for that particular day.

So the result should be like this,

+----+------+------+-----------+
| ID | Date | Name |  Sport    |
+----+------+------+-----------+
|  3 |    3 | A    | tennis    |
|  6 |    1 | C    | basketball|
|  7 |    1 | C    | tennis    |
|  8 |    1 | C    | fishing   |
|  9 |    4 | D    | football  |
+----+------+------+-----------+

This is the follow up problem listed mysql: find rows with repeated values plus condition

thank you for helping !

Community
  • 1
  • 1
Trung Nguyen
  • 65
  • 9
  • 1
    why Should A be in the result because on day 3, indeed he did not play football, but he just played one sport not two ? – mlwacosmos Sep 22 '15 at 15:55
  • we only remove the players on the day he play football+other game in one day. Since player A only play one game on day 3, we still keep him for day 3. thx – Trung Nguyen Sep 22 '15 at 15:59
  • and if he plays football + more than one game he stays or leaves ? – mlwacosmos Sep 22 '15 at 16:02
  • if he play football+more than one game (in same day), remove him. must be in the same day. so if he play football+tennis in one day, we remove those 2 rows as in player B – Trung Nguyen Sep 22 '15 at 16:05
  • lol. so in fact you want the list of people who did not play football in the day and you want the list of sports they practiced instead. right ? – mlwacosmos Sep 22 '15 at 16:06
  • basically, he practices football + other sport in one day. I remove him for that day. – Trung Nguyen Sep 22 '15 at 16:09

2 Answers2

1

If I've understood correctly, you want to obtain the dates and names of players who on that date either played only one sport or did not play football:

SELECT   Date, Name
FROM     player
GROUP BY Date, Name
HAVING   COUNT(DISTINCT Sport) = 1
      OR NOT SUM(Sport='football')

See it on sqlfiddle.

If you want to see which sports they did play and/or obtain the ID of the relevant records, you can join the above back to your player table:

SELECT * FROM player NATURAL JOIN (
  SELECT   Date, Name
  FROM     player
  GROUP BY Date, Name
  HAVING   COUNT(DISTINCT Sport) = 1
        OR NOT SUM(Sport='football')
) t

See it on sqlfiddle.

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • 1
    If `Sport` is guaranteed to be unique for each given `(Date, Name)` combination, you might prefer to `COUNT(*)` instead. – eggyal Sep 22 '15 at 16:21
  • 1
    PS: The logic is [equivalent](https://en.wikipedia.org/wiki/De_Morgan%27s_laws) to `HAVING NOT ( SUM(Sport='football') AND COUNT(*) > 1 )`, which is how you've expressed it—perhaps that's clearer in your application context. – eggyal Sep 22 '15 at 16:26
  • thank you so much, your query (2nd one ) produce the result i need. I try the HAVING NOT line that you suggested, it works great also. – Trung Nguyen Sep 22 '15 at 18:53
1

You should be looking for this:

Here, we are omitting the key record values (date + name) those match in an intersection of key record values (date + name) who played football and key record values (date + name) who did not play football

SELECT 
    p1.*
FROM
    player p1
        LEFT JOIN
    (SELECT 
        pnfb.*
    FROM
        (SELECT 
        date, name
    FROM
        player
    WHERE
        sport <> 'football') pnfb
    JOIN (SELECT 
        date, name
    FROM
        player
    WHERE
        sport = 'football') pfb ON (pnfb.date = pfb.date
        AND pnfb.name = pfb.name)) p2 ON (p1.date = p2.date AND p1.name = p2.name)
WHERE
    p2.date IS NULL;
James Jithin
  • 10,183
  • 5
  • 36
  • 51
  • you are the bomb ! :) this query work great also. thanks you so much James. Is there any book you recommend to read to improve this type of query skills? :))) – Trung Nguyen Sep 22 '15 at 18:57
  • 1
    Hey @TrungNguyen, happy that it worked. For me, this has come by practice. Try to solve more queries by your own, you will be happy once you have got the right one! Would you like to accept this answer? – James Jithin Sep 22 '15 at 18:59