dear all
i would like to get the following information out of my tables below: Which tennis player has won his first competition in 2015 in the second half of the year ?
The result I would like to see is 'djokovic'.
Can I do this with a nested query?
Thanks for your help!
Player List
---------------
ID Name
1 federer
2 nadal
3 djokovic
Competitions won
--------------
Player ID Date
1 11-Mar-15
1 12-Sep-15
2 12-Mar-15
3 15-Sep-15Sounds like homework to me. Please take a shot at it (pun intended!) and post your results
SELECT playerlist.name
FROM playerlist
GROUP BY playerlist.id
HAVING competitions.date > 1442068113
(SELECT competitions.date FROM competitions WHERE competitions.playerid = playerlist.id)
This gives an error. And many other tries did not work either. I am keen on learning and working on this since a few hours since I am new to this. constructive feedback is appreciated.
When you get an error, be sure to post the message! In this case I bet SQL is complaining that playerlist.name is not in an aggregate function nor in the group by. Is that what you're getting?
also in the second query:
SELECT competitions.date FROM competitions WHERE competitions.playerid = playerlist.id
You are referring to a table "playerlist" but it is not defined anywhere in the second query. You probably want to use a JOIN operator
Let's walk thru this.
"Which player won his first competition ..."
Hmm, so what do we need to do in SQL to determine a player's first win?
In SQL terms, what function would give you the first/earliest win?
We know we'll need the playerid, and something to determine the first win date for each player:
SELECT playerid, ?date? --?how to get the earliest date?
FROM competititons
??
"... in the second half of the year ?"
So we also need to check that the date is in the second half of the year.
That would be from July 1 on :-).
So that would involve checking that
date >= '20150701'
(SQL treats dates as literals, and date literals in SQL Server should always be in the format of 'YYYYMMDD'.)
Now, to check any condition, we'll have to use WHERE or HAVING.
So, what we end up with to get the playerid is a structure like this:
SELECT playerid, ?date? --?how to get the earliest date?
FROM competititons
??
WHERE|HAVING ?date? >= '20150701'
Once that is complete, that will give us the playerid. Then all we have to do is join back to the playerlist by that id to get the name.
ScottPletcher,
Way to go on explaining the issue while not giving the answer. It is obviously homework and that is designed to get the students to learn the concepts. What a great way to help them learn by thinking about the issue and you did a great job of explaining it without giving it away. Too many people will give the answer but that does nothing for helping someone learn. Nice job.
@scott: Thanks for your effort answering my question.
as far as i understand correctly it tried more or less what you are writing. I used the MIN (date) to get the earliest date.
SELECT player.name, MIN(competions.date) AS time
FROM player
JOIN competitions ON player.id = competitions.userid
WHERE competitions.date > 1435709024
but with this code i get player 1+3. the missing piece is how I exclude player 1.
do you have any other hint? Or somebody else?
@James (and now I also understand the post from gbritton): NO, ITS NOT HOMEWORK!! i am working now the whole day on this... (btw. CET) thanks for not doing such assmuptions (because it prevents others from replying to my post)
Unless you're working for the ATP, it's homework
gbritton: i don't know if you are very funny or the opposite. I guess the latter. of course i cannot the post the real names of the tables and fields. but it seems i would be a good teacher creating sql exercises.
Excellent, it is MIN() that you need.
But remember, the date you need to compare is that date, the min date, not every routine date in the table.
To fill out the general structure we had before. Name lookups can usually be deferred (delayed) until the very end of the processing, so we'll stick with the competitions table first.
SELECT playerid, MIN(date) AS first_win --let's give it a nice, meaningful name
FROM competititons
?WHERE|HAVING? MIN(date) >= '20150701'
thanks scott. I receive the following error:
Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
i also tried unix timestamp what i normally use.
You can't? Proprietary stuff I guess. Yes, I was trying to be funny (doesn't always work online!)
This might do it:
SELECT pl.ID
, pl.NAME
, First_Win
FROM [Player List] pl
CROSS APPLY (
SELECT MIN([Date]) First_Win
FROM [Competitions won] cw
WHERE pl.ID = cw.[Player ID]
AND [Date] >= '20150701'
) _thanks gbritton. I did not know the function cross apply before.
i was able to rename the fields/table expressions for my specific case so that an output without errors is delivered. Unfortunately my real case contains two more tables which i try now to connect to the existing ones. not without errors yet 
i will keep on trying tomowrrow.
thanks for your help!
Please stick to doing this one step at a time. I know it's tempting to try to jump straight to a full solution, but then (1) you won't understand the process of how to do it and (2) it may not be right.
Since SQL rejects:
SELECT playerid, MIN(date) AS first_win --let's give it a nice, meaningful name
FROM competititons
WHERE MIN(date) >= '20150701'
what other option do we have? I have you a HUGE hint when I wrote "WHERE/HAVING" earlier. Try this:
SELECT playerid, MIN(date) AS first_win --let's give it a nice, meaningful name
FROM competititons
HAVING MIN(date) >= '20150701'
That should work just fine (go ahead and try it out). Now all we have to do is join that to the player table, which is a normal INNER JOIN. But, to keep everything separate so it can be tested by itself itself first, we'll make our query above a "derived table" and then join it to the player table, like this:
--NOTE: when using multiple tables in one query, **always** add an "alias" you can use to refer to that table
SELECT pl.*, c.first_win --naturally you don't have to list the win date, but you can
FROM playerlist AS pl
INNER JOIN (
--find the first win for each player
SELECT playerid, MIN(date) AS first_win --add a meaningful alias name for this column
FROM competititons
WHERE MIN(date) >= '20150701'
) AS c ON c.playerid = pl.idSELECT pl.*, c.first_win --naturally you don't have to list the win date, but you can
FROM tbl_playerlist AS pl
INNER JOIN (
--find the first win for each player
SELECT MIN(date) AS first_win,player_id --add a meaningful alias name for this column
FROM tbl_competitions_won
group by player_id
having min(date) >= '20150701'
) AS c ON c.player_id = pl.id group by player_id,id,name,first_win
this vl work fine I think so...try this.
thanks scott and kiran for your replies.
this is how it works finally (only conceptual):
the missing piecing was that i was not able to exclude the winners of the first half of the year, so i decided to identify them in the first step (subquery):
SELECT player_table.id
FROM player_table
INNER JOIN competition_table ON player_table.id = competition_table.playerid
WHERE competition_table.date => 1.1.15
AND competition_table.date < 1.7.15
Around this I have the following:
SELECT player_table.id, player_table.name, competition_table.date
FROM player_table
INNER JOIN competition_table ON player_table.id = competition_table.playerid
WHERE player_table.id NOT IN
(SELECT player_table.id
FROM player_table
INNER JOIN competition_table ON player_table.id = competition_table.playerid
WHERE competition_table.date => 1.1.15
AND competition_table.date < 1.7.15)I hope player_table.id is defined with a NOT NULL constraint
The clause "having min(date) >= '20150701'" will, by itself, exclude all winners from the first half of the year (or any earlier date).
HAVING is by far the most underused part of SQL.
honestly I don't understand that function yet, but i will try to change that. thanks for pointing this out.