Unanswered: Problem with select statement[emergency]
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
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,...)
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 @@?
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.