[Nanni]

Hi to all and sorry for my english.

I'm a newbie in database and I have a problem in database design.
The goals is a a soccer database.

I have separate table for:

Season Year (id,Year)
Team Category (id,Category)
Team (id,Name,address,id coach.....)
Players (id,name,team id....)

The table are yet incomplete.

The other table are not important for my problem.
The problem is the table's relationship.

Every year some teams are relegated (Up/down) in other Category.
Every year some players change team.

I need to query...in the Season year x and in the Team Category x
which teams did play?

I need to query... in the Season year x and in the Team Category x
which players for that x team?

Thanks in advance for any help.

Regards Nanni

[Brett Kaiser]

year needs to be added to team and players table

[r937]

Originally Posted by Nanni

I need to query...in the Season year x and in the Team Category x
which teams did play?

I need to query... in the Season year x and in the Team Category x
which players for that x team?

homework assignment

[Nanni]

Hi to all and Thanks for answers.

Dear r937 this project isn't a homework assignment.

I am trying to understand something of Database and SQL.
This is my first case study.
Enclosed a screenshot of my schema.
It isn't no finished,but i have a lot of difficulty to complete
and query it.

eg how to query Table Calendario(fixture) for have a Table
with Name of Team1, Name of Team2, Goal Team1(GoalSquadraCasa),
Goal Team2(GoalSquadraOspite)?
I have name of team in separate Table(Squadre) and ID of Team instead
of name, in Table Calendario(fixture).
In the following query I get id but not the name:

SELECT DISTINCT Calendario.IdSquadraCasa AS [Squadra Casa], Calendario.IdSquadraOspite AS [Squadra Ospite], Serie.NomeSerie, Anno.Anno, Calendario.NumeroGiornata, Calendario.DescrizioneGiornata
FROM ((Serie INNER JOIN ((Anno INNER JOIN SerieAnno ON Anno.IdAnno=SerieAnno.IdAnno) INNER JOIN Stagioni ON Anno.IdAnno=Stagioni.IdAnno) ON (Serie.IdSerie=Stagioni.IdSerie) AND (Serie.IdSerie=SerieAnno.IdSerie)) INNER JOIN Calendario ON Stagioni.IdStagione=Calendario.IdStagione) INNER JOIN (Squadre INNER JOIN SquadreStagioni ON Squadre.IdSquadra=SquadreStagioni.IdSquadre) ON Stagioni.IdStagione=SquadreStagioni.IdStagione
WHERE (((Serie.NomeSerie)="SerieA") AND ((Calendario.NumeroGiornata)="Giornata N. 1") AND ((Calendario.DescrizioneGiornata)="Andata"));

Any Help for improve this schema will be very appreciated.

Regards Nanni