asked on
Issue with getting unwated duplicate records in SQL query
I have a database of baseball stats, one record per player per year, and I want to generate a list of players that meet certain criteria two years in a row. But I'm running into issues with players getting doubled up when they meet the criteria for more than two years.
The core structure looks something like this:
select PLAYER
from
TABLE a,
TABLE b
where a.YEAR = b.YEAR+1
But when a player meets the criteria for two pairs of years, like this...
Player A | 2011 | 2012
Player A | 2012 | 2013
... you wind up doubling him up. In this instance, his 2012 season would get counted twice. I'd want the Player to be on my list as such:
Player A | 2011
Player A | 2012
Player A | 2013
...but right now I'm getting:
Player A | 2011
Player A | 2012
Player A | 2012
Player A | 2013
Can anyone provide a solution for only including that middle year once?
The core structure looks something like this:
select PLAYER
from
TABLE a,
TABLE b
where a.YEAR = b.YEAR+1
But when a player meets the criteria for two pairs of years, like this...
Player A | 2011 | 2012
Player A | 2012 | 2013
... you wind up doubling him up. In this instance, his 2012 season would get counted twice. I'd want the Player to be on my list as such:
Player A | 2011
Player A | 2012
Player A | 2013
...but right now I'm getting:
Player A | 2011
Player A | 2012
Player A | 2012
Player A | 2013
Can anyone provide a solution for only including that middle year once?
MySQL ServerMicrosoft SQL ServerPostgreSQL
Last Comment
Rob
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Not exactly what I was looking for, but led me in the right direction.
No problem. Can you post how you eventually did it?
As for the grading, this should probably be a B.
I am thinking you may not be clear on EE grading guidelines for Questions.
https://www.experts-exchange.com/help.jsp?hi=400#hs=29&hi=403
Grading Guidelines
Grading at Experts Exchange is not like school. It's more like the "10-point Must" system in professional boxing; in other words, an answer is worth an A, unless it doesn't resolve your issue. If it requires you to do a little more research, or figure out one more piece of code, then it's worth a B. If you think it's not worth a B, the custom is to offer the Experts an opportunity to earn a better grade.
If this answer was the answer to your question, the proper grade to give, even if the answer is not liked, is an A. To change the grade, you can look at the directions here:
https://www.experts-exchange.com/help.jsp?hi=400#hs=29&hi=404
It says:
If you feel you have graded a solution incorrectly, click the Request Attention button and the Moderators will change it for you. If another member disputes the grade you have given, you will be asked to explain your reasoning; please respond to those requests.
I always think it is better to explain to the Author the grading system before I take a Question to the Moderators. More often than not, Authors don't really understand how EE grades are supposed to work.
Rob
As for the grading, this should probably be a B.
I am thinking you may not be clear on EE grading guidelines for Questions.
https://www.experts-exchange.com/help.jsp?hi=400#hs=29&hi=403
Grading Guidelines
Grading at Experts Exchange is not like school. It's more like the "10-point Must" system in professional boxing; in other words, an answer is worth an A, unless it doesn't resolve your issue. If it requires you to do a little more research, or figure out one more piece of code, then it's worth a B. If you think it's not worth a B, the custom is to offer the Experts an opportunity to earn a better grade.
If this answer was the answer to your question, the proper grade to give, even if the answer is not liked, is an A. To change the grade, you can look at the directions here:
https://www.experts-exchange.com/help.jsp?hi=400#hs=29&hi=404
It says:
If you feel you have graded a solution incorrectly, click the Request Attention button and the Moderators will change it for you. If another member disputes the grade you have given, you will be asked to explain your reasoning; please respond to those requests.
I always think it is better to explain to the Author the grading system before I take a Question to the Moderators. More often than not, Authors don't really understand how EE grades are supposed to work.
Rob