I'm new to this site. I'm working on my first website that contains a database. I decided to follow a lab tutorial found online to create a website in Webmatrix and then creating a web page which displays the data. I finished creating my database but now I'm having trouble displaying them correctly. Below is the code I'm currently having trouble with. The lab code given does not quote out // the PLayer.firstname to // WHERE Players.team = Teams.teamiD"; however when I do qoute it out I don't get an error, but for some reason it displays the players to be on each team. So if I were to add another team each player would be listed 4 times instead of 5. Basically the database contains 2 tables with a relationship with team and teamID. When I execute a query in the database to search for player it only shows him on one team so I'm assuming the way this code is written right now its looping each player per every team. Can anyone please help me
`@{
var db = Database.Open("Baseball");
var selectQueryString = "SELECT * FROM Players, Teams";
//@"SELECT TOP 10
// Players.firstname,
// Players.lastname,
// Teams.teamname,
// Players.runscore
// FROM Players, Teams
// WHERE Players.team = Teams.teamiD";
}
<!DOCTYPE html>
<html lang="en">
<title>Players standings</title>
<style>
h1 {font‐size: 20px;}
table, th, td {
border: solid 1px #bbbbbb;
border‐collapse:collapse;
padding:5px;
}
</style>
</head>
<body>
<h1>Players' championship ‐ standings</h1>
<table>
<thead>
<tr>
<th>Players</th>
<th>Team</th>
<th>runscore</th>
</tr>
</thead>
<tbody>
@foreach(var row in db.Query(selectQueryString)){
<tr>
<td>@row.firstname @row.lastname</td>
<td>@row.teamname</td>
<td>@row.runscore</td>
</tr>
}
</tbody>
</table>
</body>
</html>`
I can't post a picture because of the reputation points but this is how the webpage displays :
Players' championship ‐ standings
Players Team runscore David Ortiz Orioles 134 David Ortiz Red Sox 134 David Ortiz Angels 134 David Ortiz Dodgers 134 Mike Trout Orioles 132 Mike Trout Red Sox 132 Mike Trout Angels 132 Mike Trout Dodgers 132 Hanley Ramirez Orioles 102 Hanley Ramirez Red Sox 102 Hanley Ramirez Angels 102 Hanley Ramirez Dodgers 102 Yasiel Puig Orioles 4 Yasiel Puig Red Sox 4 Yasiel Puig Angels 4 Yasiel Puig Dodgers 4 Josh Hamiliton Orioles 75 Josh Hamiliton Red Sox 75 Josh Hamiliton Angels 75 Josh Hamiliton Dodgers 75
Your query is not a useful one. You've told it to get every player, and every team, and it's done that. How should it match a player with the correct team? You haven't told it, so it matches every player to every team. This is called a Cartesian join, and can be very very bad news. You need add a section on how to join the players to the correct team. You can do it two ways:
select *
from Players, Teams
WHERE Players.team = Teams.teamiD
That is the 'old-fashioned' way of doing it. The newer way, which does exactly the same thing but can be easier to read is:
select *
from Players p
inner join Trans t on t.Id = p.teamID
Look up the syntax for Joins. Joins are either inner or outer or cross, and then either left or right or full. The Wikipedia article is probably not a bad place to start. Then look at the docs for whichever database system you're using (I see a MySQL tag, so that would be here).