Problem with 3 table join

[delassus]

I have a college football database where I want to list yearly team summaries which includes the each year played with the coach and number of wins, losses, and ties. There is sometimes more than 1 coach for a season which is where I am having problems. I got the code to work for a single coach and it is here:

SELECT

games.year AS year,
coach.longname AS coach1,
SUM(games.wlt='W') AS win,
SUM(games.wlt='L') AS loss,
SUM(games.wlt='T') AS tie

from games, yearly, coach

where games.team1 = '$team'
and games.team1 = yearly.team
and games.year = yearly.year
and yearly.coach1 = coach.coachid

group by games.year

order by games.year

This code works fine but I can't get the code to work when I add another column in the yearly table for coach2

Here is the code I am trying with no luck:

SELECT

games.year AS year,
coach.longname AS coach1,
coach.longname AS coach2,
SUM(games.wlt='W') AS win,
SUM(games.wlt='L') AS loss,
SUM(games.wlt='T') AS tie

from games, yearly, coach

where games.team1 = '$team'
and games.team1 = yearly.team
and games.year = yearly.year
and yearly.coach1 = coach.coachid
and yearly.coach2 = coach.coachid

group by games.year

order by games.year

I think I am missing a basic concept that I need. Can anyone point me in the right direction?

David

[tomhath]

The problem is here. Unless coach1 and coach2 are the same it cannot match.

and yearly.coach1 = coach.coachid
and yearly.coach2 = coach.coachid

[tomhath]

WIthout seeing your table layouts I can't guess what the correct query would be. But one thing that will always cause trouble is trying to put an array inside a table row (coach1 and coach2).

I would get rid of the field for coach2 in the yearly table and add another table to link the coaches with the teams they coach by date range.

[delassus]

The dilemma I have is when there are co-coaches which happened quite a bit in the late 1800s and early 1900s. I was thinking of having a yearly table with columns as follows:

id
year

team
coach1
coach2
season_note

The purpose for this table is for my year by year summaries where I list each year with their coach, season W/L totals and a season note. It works just fine if I don't use the coach table and put the coach name right in the above mentioned fields but I like the idea of using a coaching table with just the coach name and id.

If this setup still has problems I will could look at something else but I thought I was close to getting this to work when I confirmed it works without the coach table.

David

[tomhath]

Try it this way:

and (yearly.coach1 = coach.coachid
or yearly.coach2 = coach.coachid)

[delassus]

Iwill try the code you suggest tonight when I get home from my day job. I have other applications of this same problem. I also list bowl games where I need to connect a team1 and a team2 to the same team_facts table. I will reply back tonight if this worked.

Thanks for the help.

David

[delassus]

Tom,

Your suggestion of using the "or" function should work but my PC is cranking for a very long time like it is trying to pull up millions of rows of data. Here is what I am trying but still have something wrong. The below statement works just fine when I replace the "or" with "and" but I only retrieve the rows where there are two coaches and not the years when there is only one coach.

SELECT

games.year AS year,
c1.longname AS coach1,
c2.longname AS coach2,
SUM(games.wlt='W') AS win,
SUM(games.wlt='L') AS loss,
SUM(games.wlt='T') AS tie

from games, yearly, coach c1, coach c2

where games.team1 = '$team'
and games.team1 = yearly.team
and games.year = yearly.year
and (yearly.coach1 = c1.coachid
or yearly.coach2 = c2.coachid)

group by games.year

order by games.year

Do you have any other suggestions as to why it cranking like it is trying to pull up millions of rows?

David

[Sxooter]

You should probably look at redesigning your table schema to have a many to many relationship for coaches to teams. I.e. have an intermediate table that has something like:

table mid:
team_id|year|coach_id

and then match them like:

...where team.id=mid.team_id and coach.id=mid.coach_id

That way if there's more than one it'll just kinda fall out on your lap.

[tomhath]

It might well be grinding through a lot of rows. Does the "coach" table have an index on coachid? Try rewriting it something like this (not tested):

SELECT
games.year AS year,
c1.longname AS coach1,
c2.longname AS coach2,
SUM(games.wlt='W') AS win,
SUM(games.wlt='L') AS loss,
SUM(games.wlt='T') AS tie

from games
inner join yearly
on games.team1 = yearly.team
and games.year = yearly.year
inner join coach c1
on yearly.coach1 = c1.coachid
left join coach c2
on yearly.coach2 = c2.coachid
where games.team1 = '$team'

group by games.year, c1.longname, c2.longname

order by games.year

[delassus]

Tom,

The coach table is fairly simple. It has 3 columns as follows:

coachid - primary key
longname
shortname

I thought the primary key served as the index in this case.

I am not very familiar with the inner join and have never thought of it. I will have to try what you suggest tonight and see if I can get it to work.

Do you think I need to change my table setup to run more effectively such as what Sxooter suggests? I was having problems formating the data into my templates with the many to many setup. Maybe I should attack that issue if it is more effective.

David

[delassus]

Tom,

The inner join and left join worked just fine. My data is appearing exactly as I desire it in my templates. Could you expand a little on the differences between the inner and left joins and when to use them? I have not used them before. Thanks for the help.

David