Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    2

    Soccer Database Relationship

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    year needs to be added to team and players table
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2007
    Posts
    2
    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
    Attached Thumbnails Attached Thumbnails Db.JPG  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •