A. McLean A. McLean - 6 months ago 13
SQL Question

Remove duplicate rows from custom SQL select query

I have a table set up that has a unique

Match_ID
for each data entry. However, when I want to call a custom select query that gathers the team name from the
Team
table I get duplicate rows listing two
Match_ID
s that are the same. I want to display rows uniquely in a GridView with columns
Home team
,
Away team
,
Home team score
,
Away team score
and
Game date
. I don't want to display a Team's ID in their columns but instead their Team names. However I also want to make sure I can update this GridView, since using AS has prevented me from being able to do so in the past.

Here's what Im using so far with an image showing what the query returns. I am using an SQL datasource.

SELECT MatchStatistics.Match_ID, MatchStatistics.Home_team_ID, MatchStatistics.Away_team_ID, MatchStatistics.Home_team_score, MatchStatistics.Away_team_score, MatchStatistics.Game_date,
Team.Team_name
FROM MatchStatistics INNER JOIN
Team ON MatchStatistics.Home_team_ID = Team.Team_ID OR MatchStatistics.Away_team_ID = Team.Team_ID
WHERE EXISTS
(SELECT DISTINCT Match_ID
FROM MatchStatistics AS MatchStatistics_1)


<asp:GridView ID="EnterMatchGridView" runat="server" AutoGenerateColumns="False" DataKeyNames="Match_ID" DataSourceID="SqlDataSource4" OnRowUpdating="EnterMatchGridView_RowUpdating" OnRowDeleting="EnterMatchGridView_RowDeleting">
<Columns>
<asp:BoundField DataField="Match_ID" HeaderText="Match_ID" SortExpression="Match_ID" InsertVisible="False" ReadOnly="True" />
<asp:TemplateField HeaderText="Home Team" SortExpression="Home_Team_Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Home_Team_Name") %>'></asp:TextBox>

</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("Home_Team_Name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Away Team" SortExpression="Away_Team_Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Away_Team_Name") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Away_Team_Name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Home Team Score" SortExpression="Home_team_score">
<EditItemTemplate>
<asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Home_team_score") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("Home_team_score") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Away Team Score" SortExpression="Away_team_score">
<EditItemTemplate>
<asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("Away_team_score") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("Away_team_score") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date" SortExpression="Game_date">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Game_date", "{0:dd-MM-yyyy}") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("Game_date", "{0:dd-MM-yyyy}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit/Delete">
<ItemTemplate>
<asp:LinkButton ID="BtnEdit" runat="server" CausesValidation="false" CommandName="Edit" Text="Edit" />
<span onclick="return confirm ('Are you Sure?')">
<asp:LinkButton ID="BtnDelete" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" />
</span>
</ItemTemplate>
<EditItemTemplate>
<asp:Button ID="BtnUpdate" runat="server" CausesValidation="true" CommandName="Update" ConflictDetection="OverwriteChanges" Text="Update" ValidationGroup="EnterMatchGridView" />
<asp:Button ID="BtnCancel" runat="server" CausesValidation="false" CommandName="Cancel" ConflictDetection="OverwriteChanges" Text="Cancel" />
</EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>


enter image description here

enter image description here

In short, how do I make this query return unique a unique
MATCH_ID
for each row?

Answer

Wouldn't that be...

SELECT s.Match_ID
     , s.Home_team_ID
     , s.Away_team_ID
     , s.Home_team_score
     , s.Away_team_score
     , s.Game_date
     , h.Team_name home_team
     , a.Team_name away_team
  FROM MatchStatistics s
  JOIN Team h
    ON h.Team_ID = s.Home_team_ID 
  JOIN Team a
    ON a.Team_ID = s.Away_team_ID 

???

Comments