mysql: find rows with repeated values plus condition

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 30)


• Name = name of the players


• Sport = sport they play and there can be many sports in the list; but i only focus on "football" one

This is the table

+----+------------+-------+-------------+
| 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     |
+----+------------+-------+-------------+

I want to find all the people (name and sport) who play more than 2 sports in one day and one of the sport has to be "football".

So the result should be like this,

+----+------+------+----------+
| ID | Date | Name |  Sport   |
+----+------+------+----------+
|  1 |    1 | A    | football |
|  2 |    1 | A    | soccer   |
|  4 |    2 | B    | tenis    |
|  5 |    2 | B    | football |
+----+------+------+----------+

We don't count Name "C" since he does not play football (even he play more than 2 sport in one day).

I try

SELECT * FROM player GROUP BY Date, Name HAVING count(Date) > 1;

but wont give me what i want.

PS: this post is not a duplicate post. Those answers in Finding duplicate values in MySQL
wont directly target this question. this is finding the row with repeated values and condition. please remove the "duplicate" tag so others can benefit from this question.

Answer

You should be looking for this:

Table pl1 has matching player name and date who has played football, pl2 includes the count, pl3 gets you all those players who has played football and more games on a particular date and then you fetch the matching data from pl4

SELECT 
    pl4.*
FROM

    player pl4
        JOIN
    (SELECT 
        pl2.name, pl2.date, COUNT(pl2.name) numberofgames
    FROM
        player pl2
    JOIN (SELECT 
        date, name
    FROM
        player

    WHERE
        sport = 'football') pl1 ON (pl2.name = pl1.name
        AND pl2.date = pl1.date)
    GROUP BY pl2.name , pl2.date
    HAVING numberofgames > 1) pl3 ON (pl3.name = pl4.name
        AND pl3.date = pl4.date)