3

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.

Community
  • 1
  • 1
Trung Nguyen
  • 65
  • 9
  • 1
    search the community before posting questions - http://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql – chrismillah Sep 22 '15 at 01:26
  • I read and search before posting this. Those "group by" and "count >1) only group those repeat row together. but what i want is show those repeat rows + the require that the sport has to be "football" – Trung Nguyen Sep 22 '15 at 01:47
  • please help, I dont seem to find the similar request any where. thx ! – Trung Nguyen Sep 22 '15 at 03:10
  • @r00k, this is not a similar post! – James Jithin Sep 22 '15 at 05:28
  • Re 30: Some months have 31 days. Re more than 2: Your example data and query suggest that you care about 2 or more sports. Re wont give me what i want: Why aren't you grouping etc from players who play football? Re people (name and sport) who play more than 2 sports in one day: You mean, name and sports of people who play more than 2 sports on the same day of the month. – philipxy Sep 22 '15 at 06:10
  • play more than 2 sports in the same day and one of the sport has to be football. in other words, he practices football and other sports in the same day – Trung Nguyen Sep 24 '15 at 05:02

2 Answers2

3

Try:

select
    a.*
from tbl a
join (
    select 
        date, name,
        max(case when Sport = 'football' then 1 else 0 end) val
    from tbl   
    group by date, name
    having count(date) > 1
) b on a.date = b.date and a.name = b.name
where b.val = 1

Demo sqlfiddle

Praveen
  • 8,945
  • 4
  • 31
  • 49
3

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)
James Jithin
  • 10,183
  • 5
  • 36
  • 51