need alittle help - 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

SQL Server Development (2000)

need alittle help

Author

Topic

zig
Starting Member

4 Posts

Posted - 2003-02-15 : 14:12:03

Hello all. Maybe someone can help me with alittle problem.

table 1 - A list of team names
Team
----
Mets
Marlins
Rangers
Tigers
Cubs

table 2 has team vs team
Home vs opponent
--- -- ------
mets vs marlins
mets vs cubs
cubs vs rangers
tigers vs rangers

In my query, my first column needs to be all the team names from table 1. The second column needs to be a count of how many times a home team has played another team. example for cubs:

Mets - 0
Marlins - 0
Rangers - 1
Tigers - 0

After much reading, I came to this query.

Select table1.Team, Opp_CountTable.Opponent_Count
From table1
Left Outer Join
(Select table2.otherTeam As opponentTeam,
Count(table2.otherTeam) As Opponent_Count
From table2
Where table2.homeTeam = 'cubs'
Group By table2.otherTeam) As Opp_CountTable
ON table1.Team = Opp_CountTable.otherTeam
WHERE table1.Team <> 'cubs'

my results are this:

Mets - NULL
Marlins - NULL
Rangers - 1
Tigers - NULL

Now I pretty much guessed at the query after reading alot, and I'm wondering if i'm going about the solution in the correct way? Also, if the query is ok, then is there is a way to ouput zero's inplace of the NULL's?

Thanks
zig

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-15 : 15:40:17

select home.team, away.team, count(*)
from table1 home
cross join table1 away
left outer join table2 match
on home.team = match.hometeam and away.team = match.otherteam
where home.team <> away.team
group by home.team, away.team

That should give everything - you can put cubs in the where clause if you like.

from table1 home
cross join table1 away

gives all the possible matches (where clause excludes team playing itself)
The left outer join gives an entry for each matche.
The group by and count(*) does the rest.

If you just want a single team you can change your query to

select Select table1.Team, coalesce(Opp_CountTable.Opponent_Count, 0)

or maybe

select h.team, count(*)
from table1 h left outer join table2 a
on a.hometeam = 'cubs' and h.team = a.otherteam
group by h.team

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

zig
Starting Member

4 Posts

Posted - 2003-02-15 : 18:57:00

Thanks nr for the reply

The cross join works great, but instead of zero's or the nulls I had originally, I now get ones for teams that havent been played against, which wont work in this case, becuase i would never know if they played one game or none. But the coalesce function does the trick!

I was wondering though if using the derived table like i have is a good way to write the query? Or is it not recommended? Or is it just less effienct than writing the cross join?

Thanks again for your help
zig

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-15 : 21:59:54

Trying changing the COUNT(*) to COUNT(match.hometeam) and you should have the answer you need.

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-16 : 03:04:08

Oops, ta

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

zig
Starting Member

4 Posts

Posted - 2003-02-16 : 19:59:21

Thanks nr and Jeff. The query works perfectly.

zig

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources