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

SQL Server 2000 Forums

Transact-SQL (2000)

Join problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-03-18 : 06:20:47

Jason writes "Hi there, I have a problem with a particular query.

I have two tables as follows:

create table players
(
playerid numeric(5, 0) identity primary key,
name varchar(50) not null,
age numeric not null
)

create table score
(
scoreid numeric(5,0) identity primary key,
score numeric(5,0) not null,
tournamentid numeric(5) null,
courseid numeric(5) null,
matchid numeric(5) null,
playerid numeric(5) null,
holeid numeric(5) null,
)

I want to return details of each players name and age and the total score for them.

e.g. Name:Bob Age: 22 Total score:75
Name: Rob Age: 20 Total score: 77
etc..

I have tried this:

select name, age, sum(score.score) from players, score where players.playerid = score.playerid AND score.playerid = players.playerid

The name and ages are correct but...the sum of all the scores are displayed for every players result.`
i.e.

Bob 22 152
Rob 20 152

Could you tell me what I'm doing wrong???

Thanks in advance.

Jason"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-18 : 07:29:55

First, you should use the JOIN syntax to join tables.
Second, you don't need your JOIN condition twice.
Third, you need a group by.


select
    p.name,
    p.age,
    sum(s.score) as total_score
from
    players p
    inner join score s
        on p.playerid = s.playerid
group by
    p.name,
    p.age

Note: This will only return players that have scores. If you want all the players, even the ones without scores, use a left outer join.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources