Solved

Coomplex SQL Query

Posted on 2009-03-30
8
216 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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
 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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