Need help with Inner Join / Count on sports db

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

General SQL Server Forums

New to SQL Server Programming

Need help with Inner Join / Count on sports db

Author

Topic

chien_fu
Starting Member

16 Posts

Posted - 2007-11-04 : 09:23:45

I'm very new to this so I will start from scratch and would love any advice from anyone who is more knowledgable than I.

So I have two tables, Game_Schedule and Standings

Relevant columns in Game_Schedule:
Team1 (Represents home team)
Team2 (Represents away team)
Win ('true' represents win for home team)
Loss ('true' represents win for away team)

Relevant column in Standings:
T_Name
Wins
T_Tier

THE OBJECTIVE is to count the number of wins (true values in 'Game_Schedule') for each team in 'Standings' and list them ordered by T_Tier and then Wins.

Sounded easy at first but I haven't had any success.

Here is my LATEST ATTEMPT:

SELECT (Select DISTINCT standings.T_Name from standings), Game_Schedule.Team1, standings.T_Tier, Game_Schedule.Team2, Game_Schedule.Win, Game_Schedule.Loss, (Select Count(*) FROM Game_Schedule WHERE (standings.T_Name=Game_Schedule.Team1 AND Game_Schedule.Win=1) OR (standings.T_Name=Game_Schedule.Team2 AND Game_Schedule.Loss=1)) AS Win_Counter FROM standings, Game_Schedule ORDER BY T_Tier ASC, Win_Counter DESC

Here is an INNER JOIN attempt that also did not work:

SELECT DISTINCT T_Name, T_Tier, Wins, Game_Schedule.Team1, Game_Schedule.Team2, Game_Schedule.Win, Game_Schedule.Loss FROM standings INNER JOIN Game_Schedule ON standings.T_Name=Game_Schedule.Team1 OR standings.T_Name=Game_Schedule.Team2 ORDER BY Game_Schedule.Win DESC

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-04 : 09:29:47

Assuming that Team1 column also contains Team2 name and that each would have transposed win/loss TRUEs...
assuming Team1 name = T_Name in the standings table
assuming 'TRUE' is a text field and not a 1/0 bit field..

Select
T_Tier,
Team1,
Sum(Case When Win = 'True' then 1 else 0 end) as WINS,
Sum(Case When Loss = 'TRUE' then 1 else 0 end) as LOSSES
From Game_Schedule inner join Standings
on Team1 = T_Name
Group by T_Tier,Team1
Order by T_Tier

chien_fu
Starting Member

16 Posts

Posted - 2007-11-04 : 09:55:17

Quick response! Thanks,
That looks like it will return Wins for Team1. ie, games that that team has won at home only.

Standings.T_Name could be equal to either Team1 or Team2. And I want to know:

If Standings.T_Name = Team1 AND win=true OR Standings.T_Name = Team2 AND loss=true THEN WIN_COUNT = WIN_COUNT + 1

But how can put this statement into my query? I liked the "Sum" term you had there that sounds like something I could use...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-04 : 10:10:33

I assumed that for every Team1 Loss there would a row entry where Team2 was in the Team1 column showing a win.

Maybe something more like this will work,:

Select
T_Tier,
Team1 as TeamName,
Sum(Case When Win = 'TRUE' then 1 else 0 end) as WINS,
Sum(Case When Loss = 'TRUE' then 1 else 0 end) as LOSSES
From Game_Schedule inner join Standings
on Team1 = T_Name
Group by T_Tier,Team1
Union ALL
'get the team2 wins by reflect team1 loss as a team2 win
Select
T_Tier,
Team2 as TeamName,
Sum(Case When Loss = 'TRUE' then 1 else 0 end) as WINS,
Sum(Case When Win = 'TRUE' then 1 else 0 end) as LOSSES
From Game_Schedule inner join Standings
on Team2 = T_Name
Group by T_Tier,Team2

chien_fu
Starting Member

16 Posts

Posted - 2007-11-04 : 11:01:37

Wow, that looks perfect..

I try it out an get: error '80004005'

this looks like it could be lots of things, mostly connection errors, it doesn't specify anything else about the error, but it does reference the line of the SQL query.

I have had good success connecting to this database using my existing connection settings. Would something in this SQL statement deserve this error?

chien_fu
Starting Member

16 Posts

Posted - 2007-11-04 : 11:30:40

Here is some sample data from the two tables and what I want to become of it. If there is any better way of doing it, different table setup.. etc.. I am open to all suggestions.

Table #1: Standings

T_Name T_Tier Wins (empty column)
-----------------------------------------
Bangor 1
Brunswick 3
Wiscasset 2
Portland 1

Table #2: Game_Schedule

Team1 Team2 Win(Team1 Win) Loss(Team2 Win)
-----------------------------------------------
Bangor Brunswick 1 0 < represents win for Bangor
Wiscasset Portland 0 1 < represents win for Portland
Portland Bangor 1 0 < win for Portland
Portland Brunswick 0 1 < win for Brunswick
Brunswick Portland 1 0 < win for Brunswick

Desired outcome:

Team_Name Number_Of_Wins Tier
---------------------------------
Bangor 1 1
Portland 1 1
Wiscasset 0 2
Brunswick 2 3

Not really enough data to tell, but the desired outcome table is sorted first by Tier and then by number of wins.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-04 : 12:25:40

This works for me:
[Code]

go
Create Table Standings_TEST (
T_Name char(15) not null,
T_Tier char(1) not null,
Wins int null)

GO

Insert Into Standings_TEST(T_Name,T_Tier)
Select 'Bangor','1' Union ALL
Select 'Brunswick','3' union all
Select 'Wiscasset','2' union all
Select 'Portland','1'

go
Create Table Game_Schedule_TEST (
Team1 char(15) not null, Team2 char(15) not null,
Win bit not null, Loss bit not null)

Insert Into Game_Schedule_TEST (Team1,Team2,WIn,Loss)
Select 'Bangor','Brunswick', 1,0 Union ALL
Select 'Wiscasset','Portland',0,1 union ALL
Select 'Portland','Bangor',1,0 UNION ALL
Select 'Portland','Brunswick',0,1 UNION ALL
Select 'Brunswick','Portland',1,0

GO

Select TeamName,Sum(WINS) as WINS,Sum(LOSSES) as Losses,T_Tier
FROM (
Select T_Tier,
Team1 as TeamName,
Case When Win = 1 then 1 else 0 end as WINS,
Case When Loss = 1 then 1 else 0 end as LOSSES
From Game_Schedule_Test inner join Standings_Test
on Team1 = T_Name
Union ALL

Select T_Tier,
Team2 as TeamName,
Case When Loss = 1 then 1 else 0 end as WINS,
Case When Win = 1 then 1 else 0 end as LOSSES
From Game_Schedule_Test inner join Standings_Test
on Team2 = T_Name ) as TeamTotal
Group by TeamName,T_Tier
ORder by T_Tier
GO

Drop Table Standings_Test
Drop Table Game_Schedule_Test

with this output:
TeamName Wins Losses Tier
Bangor 1 1 1
Portland 2 2 1
Wiscasset 0 1 2
Brunswick 2 1 3

SO this is the query you could try :


Select TeamName,Sum(WINS) as WINS,Sum(LOSSES) as Losses,T_Tier
FROM (
	Select T_Tier,
		Team1 as TeamName,
		Case When Win = 1 then 1 else 0 end as WINS,
		Case When Loss = 1 then 1 else 0 end as LOSSES
	From Game_Schedule inner join Standings
	on Team1 = T_Name	
	Union ALL

	Select 	T_Tier,
		Team2 as TeamName,
		Case When Loss = 1 then 1 else 0 end as WINS,
		Case When Win = 1 then 1 else 0 end as LOSSES
	From Game_Schedule inner join Standings
	on Team2 = T_Name ) as TeamTotal
Group by TeamName,T_Tier
ORder by T_Tier
[/code]

which has your stated table names and apparent structure

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources