Hi all,
I have a challenge (not a problem). Simplifing it as far as i could, i
have this situation:
Table Persons
Id (PK)
Name
Table Scores
FK_Person (PK)
Period (PK)
Value
Now, i want to make a stored procedure that returns the scores for all
people in a certain period. So i have:
CREATE PROCEDURE [dbo].[ScorePerson]
(@.Period Int)
AS
SELECT dbo.People.Name, dbo.Scores.[Value]
FROM dbo.People LEFT OUTER JOIN
dbo.Scores ON dbo.People.Id =
dbo.Scores.FK_person
WHERE dbo.Scores.Period = @.Period
GO
BUT: if a person has no score for a certain period, he will not show
up in the result set at all, but i do want him to. So what i need to
do is first get the subset for a period from the table Scores and THEN
Left join that to the persons, so i always get all persons and only a
value for the the score if there is one.
How can i do this? I could store the results in a temporary table but
that just feels 'unpure', wrong. Is there any way i can get this in a
single pass? Can i for example join two stored procedures with
parameters together in another SP that passes these params on to them?
TIA,
Gert-JanIf you have no Periods table, you can get the list of periods based on your
Scores table. Of course, this technique will only return periods with at
least one row in the Scores table so you'll need a Periods table if you need
to include periods with no scores. Below is an example:
SELECT
dbo.People.Name,
dbo.Scores.[Value]
FROM dbo.People
LEFT JOIN dbo.Scores ON
dbo.People.Id = dbo.Scores.FK_person AND
dbo.Scores.Period = @.Period
CROSS JOIN
(SELECT DISTINCT Period
FROM Scores) AS Periods
WHERE Periods.Period = @.Period
--
Hope this helps.
Dan Guzman
SQL Server MVP
"G.J. v.d. Kamp" <gjvdkamp@.hotmail.com> wrote in message
news:d49d68a1.0411250718.59c0b023@.posting.google.c om...
> Hi all,
> I have a challenge (not a problem). Simplifing it as far as i could, i
> have this situation:
> Table Persons
> Id (PK)
> Name
> Table Scores
> FK_Person (PK)
> Period (PK)
> Value
> Now, i want to make a stored procedure that returns the scores for all
> people in a certain period. So i have:
> CREATE PROCEDURE [dbo].[ScorePerson]
> (@.Period Int)
> AS
> SELECT dbo.People.Name, dbo.Scores.[Value]
> FROM dbo.People LEFT OUTER JOIN
> dbo.Scores ON dbo.People.Id =
> dbo.Scores.FK_person
> WHERE dbo.Scores.Period = @.Period
> GO
> BUT: if a person has no score for a certain period, he will not show
> up in the result set at all, but i do want him to. So what i need to
> do is first get the subset for a period from the table Scores and THEN
> Left join that to the persons, so i always get all persons and only a
> value for the the score if there is one.
> How can i do this? I could store the results in a temporary table but
> that just feels 'unpure', wrong. Is there any way i can get this in a
> single pass? Can i for example join two stored procedures with
> parameters together in another SP that passes these params on to them?
> TIA,
> Gert-Jan|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
You also need to read a basic book on data modeling and the ISO-11179
naming conventions. How many different names did you post for the same
data element? Why are tables that share a data element modeled as if
they had no DRI relationships in your personal pseudo-code?
CREATE TABLE Persons
(player_id INTEGER NOT NULL PRIMARY KEY,
name CHAR(35) NOT NULL);
CREATE TABLE Games
(player_id INTEGER NOT NULL
REFERENCES Persons (player_id),
period_nbr INTEGER NOT NULL,
score INTEGER NOT NULL CHECK (score >= 0));
>> I want to make a stored procedure that returns the scores for all
people in a certain period. <<
Why do you still think in procedural terms in a non-procedural
language? Why not a VIEW?
CREATE VIEW TotalScores (player_name, period_nbr, score_tot)
AS
SELECT P.player_name, G.period_nbr, SUM(G.score_tot)
FROM Persons AS P
LEFT OUTER JOIN
Games AS G
ON P.player_id = G.player_id
GROUP BY player_id, period_nbr;
Then you can use this query
SELECT player_name, @.my_period_nbr, score_tot
FROM TotalScores
WHERE @.my_period_nbr = period_nbr;
A zero means he played and did not score; a NULL means he did not play
at all.|||On 25 Nov 2004 10:39:49 -0800, --CELKO-- wrote:
(snip)
>CREATE VIEW TotalScores (player_name, period_nbr, score_tot)
>AS
>SELECT P.player_name, G.period_nbr, SUM(G.score_tot)
> FROM Persons AS P
> LEFT OUTER JOIN
> Games AS G
> ON P.player_id = G.player_id
> GROUP BY player_id, period_nbr;
Hi Joe,
Maybe you should have tested this before posting!
Server: Msg 170, Level 15, State 1, Procedure TotalScores, Line 8
Line 8: Incorrect syntax near ';'.
(Okay, this is buggy behaviour of SQL Server - but this IS a SQL Server
group, after all!)
Remove the semicolon; retry:
Server: Msg 207, Level 16, State 3, Procedure TotalScores, Line 3
Invalid column name 'player_name'.
Server: Msg 207, Level 16, State 1, Procedure TotalScores, Line 3
Invalid column name 'score_tot'.
Server: Msg 209, Level 16, State 1, Procedure TotalScores, Line 3
Ambiguous column name 'player_id'.
Change player_name to name and score_tot to score in the SELECT and add P.
in front of player_id in the GROUP BY; retry:
Server: Msg 8120, Level 16, State 1, Procedure TotalScores, Line 3
Column 'P.name' is invalid in the select list because it is not contained
in either an aggregate function or the GROUP BY clause.
Okay, add P.name to the group by (or enclose it in an aggregate function),
retry and finally the view is made (whew!).
>Then you can use this query
>SELECT player_name, @.my_period_nbr, score_tot
> FROM TotalScores
> WHERE @.my_period_nbr = period_nbr;
>A zero means he played and did not score; a NULL means he did not play
>at all.
Let's enter some data to test it. Two persons (Joe and Hugo). Both have a
score in period 1, Joe has a score in period 2 and Hugo in period 3.
insert Persons values(1,'Joe')
insert Persons values(2,'Hugo')
insert Games values (1, 1, 1)
insert Games values (2, 1, 2)
insert Games values (1, 2, 3)
insert Games values (2, 3, 0)
GO
declare @.my_period_nbr int
set @.my_period_nbr = 1
SELECT player_name, @.my_period_nbr, score_tot
FROM TotalScores
WHERE @.my_period_nbr = period_nbr;
set @.my_period_nbr = 2
SELECT player_name, @.my_period_nbr, score_tot
FROM TotalScores
WHERE @.my_period_nbr = period_nbr;
set @.my_period_nbr = 3
SELECT player_name, @.my_period_nbr, score_tot
FROM TotalScores
WHERE @.my_period_nbr = period_nbr;
Does this return the data that the poster asked for? Does this return Hugo
in period 2 and Joe in period 3, even though they have no score in that
period?
Unfortunately - no.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 25 Nov 2004 07:18:55 -0800, G.J. v.d. Kamp wrote:
(snip)
>BUT: if a person has no score for a certain period, he will not show
>up in the result set at all, but i do want him to. So what i need to
>do is first get the subset for a period from the table Scores and THEN
>Left join that to the persons, so i always get all persons and only a
>value for the the score if there is one.
Hi Gert-Jan,
This is actually lots simpler than you think!
SELECT dbo.People.Name, dbo.Scores.[Value]
FROM dbo.People LEFT OUTER JOIN
dbo.Scores ON dbo.People.Id =
dbo.Scores.FK_person
AND dbo.Scores.Period = @.Period
(The only change is to move the filter condition for period to the join
condition!!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi all,
Thanks everyone for your time.
Dan, works like a charm, thanks! It obviously does help to have a
thorough foundation in math.
Celko, i only described the problem in a generic way, i thought it
would be a common problem a person more experienced than me would
recognize. (and they did). Also, a view doesn't take parameters as far
as i know. THe point about SQL not being a procedural language is
usually a good one, personally i don't think very highly of people who
break out cursors for the simplest of inserts. But i don't think it's
just in this case. Thanks anyway.
But of course first prize goes to Hugo for the lean of going about it.
I like lean, and now i like Hugo (In a friendly way, don't worry!)
Thanks!
Regards GJ
Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<h5qcq09cmd963c7vutuq1v2ru9gqdd7rld@.4ax.com>...
> On 25 Nov 2004 07:18:55 -0800, G.J. v.d. Kamp wrote:
> (snip)
> >BUT: if a person has no score for a certain period, he will not show
> >up in the result set at all, but i do want him to. So what i need to
> >do is first get the subset for a period from the table Scores and THEN
> >Left join that to the persons, so i always get all persons and only a
> >value for the the score if there is one.
> Hi Gert-Jan,
> This is actually lots simpler than you think!
> SELECT dbo.People.Name, dbo.Scores.[Value]
> FROM dbo.People LEFT OUTER JOIN
> dbo.Scores ON dbo.People.Id =
> dbo.Scores.FK_person
> AND dbo.Scores.Period = @.Period
> (The only change is to move the filter condition for period to the join
> condition!!)
> Best, Hugo|||Celko,
Your solution will probably work as well, but i just like to keap my
asp code as neat as possible, so i move all the logic to the
SQL-server as much as i can. Also, in my real world problem, it would
theoretically be possible to have a zero score as a value.
But thanks anyway.
Regards GJ
jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0411251039.116b316d@.posting.google.com>...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications.
> You also need to read a basic book on data modeling and the ISO-11179
> naming conventions. How many different names did you post for the same
> data element? Why are tables that share a data element modeled as if
> they had no DRI relationships in your personal pseudo-code?
> CREATE TABLE Persons
> (player_id INTEGER NOT NULL PRIMARY KEY,
> name CHAR(35) NOT NULL);
> CREATE TABLE Games
> (player_id INTEGER NOT NULL
> REFERENCES Persons (player_id),
> period_nbr INTEGER NOT NULL,
> score INTEGER NOT NULL CHECK (score >= 0));
> >> I want to make a stored procedure that returns the scores for all
> people in a certain period. <<
> Why do you still think in procedural terms in a non-procedural
> language? Why not a VIEW?
> CREATE VIEW TotalScores (player_name, period_nbr, score_tot)
> AS
> SELECT P.player_name, G.period_nbr, SUM(G.score_tot)
> FROM Persons AS P
> LEFT OUTER JOIN
> Games AS G
> ON P.player_id = G.player_id
> GROUP BY player_id, period_nbr;
> Then you can use this query
> SELECT player_name, @.my_period_nbr, score_tot
> FROM TotalScores
> WHERE @.my_period_nbr = period_nbr;
> A zero means he played and did not score; a NULL means he did not play
> at all.|||"G.J. v.d. Kamp" <gjvdkamp@.hotmail.com> wrote in message news:d49d68a1.0411260025.681e9a65@.posting.google.c om...
> Also, a view doesn't take parameters as far
> as i know.
Views don't take parameters in the same way as procedures and functions, but you can still use your parameters...
Select *
from myView
where myView.col1 = @.myParameter ;
The view is created without the parameter @.myParameter, but it's easy to write queries on the view that use them.
--
Paul Horan
Sr. Architect
VCI Springfield, Mass
www.vcisolutions.com
pete_m:
