Help with Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Query

Author

Topic

bcc86
Starting Member

3 Posts

Posted - 2011-03-23 : 11:51:01

Hello,

I'm not sure if this is the correct place to post this, as I am a total n00b at SQL, so if it is in the incorrect forum please feel free to move it.

I currently work for an independent league baseball team and am trying to use sql as a statistical analysis tool for our teams batting stats.

As I have done in the past, I have manually gone in copied our stats and added each players ID # by hand in a csv before importing into sql.

However I would like to move past this stage, and have created two separate tables. One table has the players full name and ID number.

The other table has the players name and their game by game statistics.

What I would like to do is use a join query so that the player's ID number doesn't have to be entered manually anymore, and I could just import the daily statistics without going through the tedious process of using a VLOOKUP in csv.

Below is an example

LOOKUP TABLE
Name ID
Alvarez 1
Thomas 2
Young 3

STATS TABLE
Name AB H BB K
Alvarez 2 1 0 0
Thomas 3 0 0 0
Young 3 1 0 0

and I would like to get the results to look like this:

RESULT TABLE
Name ID AB H BB K
Alvarez 1 2 1 0 0
Thomas 2 3 0 0 0
Young 3 3 1 0 0

I have tried multiple join queries, but I can't seem to get the results I want. I am new at SQL, so please be patient with me, and any advice anyone has would be greatly appreciate.

Thanks in advance.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-23 : 12:16:30

You Lookup Table should have the the First Name, Last Name, etc. about the player, uniquely identified by the ID.
That way if a player changes his name to "OCHOCINCO" you only have to channge it in one place
That ID is what should appear in the Stats table, so we know just which "Jones" we're talking about. After that

SELECT p.Name,p.ID,s.AB,s.H,s.BB,s.K
FROM
players p
INNER JOIN
stats s
ON p.id = s.id

Jim

Everyday I learn something that somebody else already knew

bcc86
Starting Member

3 Posts

Posted - 2011-03-23 : 16:48:36

quote:
Originally posted by jimf

You Lookup Table should have the the First Name, Last Name, etc. about the player, uniquely identified by the ID.
That way if a player changes his name to "OCHOCINCO" you only have to channge it in one place
That ID is what should appear in the Stats table, so we know just which "Jones" we're talking about. After that

SELECT p.Name,p.ID,s.AB,s.H,s.BB,s.K
FROM
players p
INNER JOIN
stats s
ON p.id = s.id

Jim

Everyday I learn something that somebody else already knew

Jim,

This works great. However now when I try to to SUM all the categories and group by player id, the totals are doubled as to what they should be. I simply could just divide by two for all categories, but just so I can learn can you show me where in the formula it is causing the totals for each statistical category to be summed twice?

Thanks again for your help.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-23 : 18:35:37

It probably means that you're missing a join somewhere. Is there another column that links the two tables?

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources