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.

Improve this question
6
  • 1
    search the community before posting questions - stackoverflow.com/questions/688549/… Commented Sep 22, 2015 at 1: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" Commented Sep 22, 2015 at 1:47
  • please help, I dont seem to find the similar request any where. thx ! Commented Sep 22, 2015 at 3:10
  • @r00k, this is not a similar post! Commented Sep 22, 2015 at 5: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. Commented Sep 22, 2015 at 6:10

2 Answers 2

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

good answer bro. its not asked by me, but I appreciate your answer
thank you so much. this give me what i want. I have another follow up problem of this one where to find the list that excludes these players (who play football+other game(s) in one day for that day) stackoverflow.com/questions/32721210
@TrungNguyen, looks like your homework. Glad that it worked! Please accept the answer.
@TrungNguyen, thanks for accepting the answer!
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

Improve this answer

1 Comment

great work Praveen. thank you. your code is nice and clean !

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.