Solved

Coomplex SQL Query

Posted on 2009-03-30
8
210 Views
Last Modified: 2012-05-06
I have to create a query, and I am in over my head on this one.  I am not sure even how to start.
Here is my situation.
I have three tables:  cl_players, cl_stats, cl_points
The cl_players table has a playerid field that contains a unique identifier per player, as well as their last name and jersey number.
The cl_points table has the same playerid and a points column
The cl_stats table has the following fields:
     playerid (same as other playerid fields mentioned above)
     gameid
     shots
     goals
     assists
     lbcause
     lbcontrol
     hits
     turnover
     penalties
     penaltyminutes
When I insert stats via a stored procedure from the asp.net page, I collect the playerid (from cl_players), the game id (from another table, which we dont really care about for this query), and all of the fields in the cl_stats table.  I collect all of these values from controls on the aspx page.

Now I want to create a gridview that contains all of the stats, but instead of the playerid, I want to use the player last name and number from the cl_players table.
Now the complicated part is that for each game, there is a record for each player, so in the query, I need to gather all of the rows, per player, total the stats per column and return a data set that contains the following fields:
     lastname (from cl_players via playerid)
     number (from cl_players via playerid)
     total shots (totaled from cl_stats via playerid)
     total goals (totaled from cl_stats via playerid)
     total assists (totaled from cl_stats via playerid)
     total lbcause (totaled from cl_stats via playerid)
     total lbcontrol (totaled from cl_stats via playerid)
     total hits (totaled from cl_stats via playerid)
     total turnover (totaled from cl_stats via playerid)
     total penalties (totaled from cl_stats via playerid)
     total penaltyminutes (totaled from cl_stats via playerid)
     total points (from cl_points via playerid)

If I can get the query that returns the data, I can handle the rest.  Any ideas?
0
Comment
Question by:TeamEnova
  • 4
  • 3
8 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 24025553
what if > 1 player has the same last name?

As an aside, do you know you won't be able to run any updates/deletes on a gridview that doesn't contain the primary key for the target table? This may not apply in this case but I thought I'd throw it in there while you are in the early stages just in case.
0
 
LVL 2

Author Comment

by:TeamEnova
ID: 24025574
QPR,
Thanks for the response.  The last names are ok because most of the data is pulled with the playerid that is a uniqely assined (identity field) in the cl_players table.  So really, each row in the datagrid will be based on the playerid.  But instead of displaying the playerid, I want to display the player's lastname and their jersey number.

As far as updates and deletes, I wont be doing any of that from this  datagrid.  The next step in my process is that when a player's name is selected from the datagrid, it will display, viea a detailview, of the statistics from the cl_stats table, per game (gameid).

Does that make sense?
0
 
LVL 29

Assisted Solution

by:QPR
QPR earned 225 total points
ID: 24025590
that's ok then you can just set the playerid visible to false in the datagrid.... or uncheck automatically create fields (or something like that) and leave the playerid filed behind.

Do you already know the columns required to join the tables? If so you are half way there.
The rest would just be...

select p.lastname, p.number, sum(s.[total shots], etc etc with all the sums
from ci_players P
inner join ci_stats s
     on p.something = s.something
group by p.lastname, p.numbers
0
 
LVL 2

Author Comment

by:TeamEnova
ID: 24025599
As you can tell, Im not that great at SQL, but am I correct in assuming that since all of the data is entered into the two child tables (cl_stats and cl_points) based on the playerid listed in cl_players, that the join would be on the playerid in the two child tables (cl_stats and cl_points)?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 275 total points
ID: 24025632
This will do
SELECT pl.lastname,

pl.number,

sum(st.shots) as total_shots,

sum(st.goals) as total_goals,

sum(st.assists) as total_assists,

sum(st.lbcause) as total_lbcause,

sum(st.lbcontrol) as total_lbcontrol,

sum(st.hits) as total_hits,

sum(st.turnover) as total_turnover,

sum(st.penalties) as total_penalties,

sum(st.penaltyminutes) as total_penaltyminutes,

po.points

FROM cl_players pl, cl_stats st, cl_points po

WHERE pl.playerid = st.playerid

and pl.playerid = po.playerid

group by pl.lastname,pl.number,po.points

Open in new window

0
 
LVL 29

Expert Comment

by:QPR
ID: 24025634
if both ci_stats and ci_points contain the same field (key) then yes you can retrieve all the values using joins based on this key.

If you set a field in a table to be a unique key (like an id) this is known as a primary key.
When you have this same key also included in another table(s) then it is known as a foreign key.
You can retrieve data from both the tables by comparing the values of the keys.

so yes....
select etc etc
from ci_players p
inner join ci_stats s
on p.playerid = s.playerid
inner join ci_points pts
on p.playerid = pts.playerid

from my earlier code you will note that I "grouped by" both lastname and number.
You have to do this when you are also selecting an "aggregate" such as sum, count, average

the letters I assigned after the table names are called aliases and save you having to type
select ci_players.lastname
by aliasing the table as "p" you can use the shorthand way.
You always need to specify either an alias or the full table name when using the key as they both have the same field name (playerid) and will error as "ambiguos name" if left out
0
 
LVL 2

Author Comment

by:TeamEnova
ID: 24025670
Wow, nice work guys.  With a little tweaking (mainly because i gave you some wrong fieldnames, mybad), it works great!  I am gonna split the points since both of you had the same solution, but with a little more towards rejegan77, since he actually provided the code.  I appreciate both of your work and it was very educational.  This will come in so handy as this is just the first of several queries I have to run against this db.

Thanks so much!
0
 
LVL 2

Author Closing Comment

by:TeamEnova
ID: 31564681
Hell of a job, guys.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now