Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2013

    Unanswered: Problem with select statement[emergency]

    Hi guys,
    This is my select statement
    SELECT t.Name AS HomeTeam, f.Stadium, f.Date_Of_Match, CONVERT(varchar,f.Start_Time, 100) AS Start_Time, p.First_Name +' '+p.Last_Name AS Referee, c.Name AS City, d.Name AS Division, l.Name AS League, s.Name AS Season
    FROM SL_City c
    JOIN SL_Fixture f ON f.City_Id = c.City_Id
    JOIN SL_Referee r ON r.People_Id = f.Referee_Id
    JOIN SL_People p ON p.People_Id = r.People_Id
    JOIN SL_Season s ON s.Season_Id = f.Season_Id
    JOIN SL_Division d ON d.Division_Id = f.Division_Id
    JOIN SL_League l ON l.League_Id = d.League_Id
    JOIN SL_Home_Fixture hf ON hf.Fixture_Id = f.Fixture_Id
    JOIN SL_Away_Fixture af ON af.Fixture_Id = f.Fixture_Id
    JOIN SL_Team t ON t.Team_Id = hf.Team_Id
    WHERE c.Name='London'
    One fixture i have two team that are assigned home and away
    i have table structure as follow (just some table in my database):
    Table SL_Fixture (FixtureId,Date,Time,Stadium,CityID,...)
    Table SL_Team(TeamID,TeamName,CityID,...)
    Table SL_Home_Fixture(FixtureID,TeamID,...)
    Table SL_Away_Fixture(FixtureID,TeamID,...)
    Table SL_City(CityID,CityName)

    And i want to select the game play in particuluar city
    i did that, but i want to show the HomeTeamName and AwayTeamName and i can't find the way to do that
    The result as something like that

    NameOfHomeTeam | NameOfAwayTeam | Stadium | DateOfMatch | StartTime | Referee | City | Division | League | Season
    Can someone give me some a hand or some idea to do that @@?
    thank alot.
    Last edited by ducpmz; 11-10-13 at 09:16.

  2. #2
    Join Date
    Jan 2013
    Provided Answers: 1

    Are you cheating on your homework?

    This problem has been posted over and over, year after year. It is a homework assignment, isn't it? And you want us to help you cheat. What we will do find your school and report you.

    You were too rude to post DDL.

    What you have done is wrong. The data element names are vague and violate ISO-11179 rules. Your use of “name” for everything is a violate of basic data modeling and the Law of Identity. If you have never had a course in Logic, this is “To be is to be something in particular. To be nothing in particular or everything in general is to be nothing at all.”

    It looks like you split the “match_timestamp” into “match_date” and “match_time” in another violation of basic design principles (it is called attribute splitting – one measurement is in two or more columns). Why do you think that “people” is a precise set that plays a roles in a valid data model? It is not; it is too generic and vague.

    Why did you use joins to tables that are attributes, not entities? Things like “season_name”, “division_id” and “league_name” would be in CHECK () constraints or REFERENCES.

    Where is the Matches table?

  3. #3
    Join Date
    Oct 2013

    All Work done

    Sorry guys, i did it but dont have time to announcement in this thread.
    thanks guys for helpping.
    i'm sorry so much

Tags for this Thread

Posting Permissions

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