title: How to display data from two SQL tables in one GridView in Web Forms?
body: <p>In my SQL Server Express I have <code>Recipients</code>, <code>MailingLists</code> and <code>RecipientMailingList</code> (<code>Recipient</code>'s ID and <code>MailingList</code>'s ID) tables. Each recipient is assigned to a few mailing lists. I want to display all recipients' info in a <code>GridView</code>. I wanted to this by dragging and dropping the table from Server Explorer to the <code>.aspx</code> source code. However, what I got is a <code>GridView</code> without any information on <code>MailingLists</code> as those are in other tables.</p>
  
  <pre><code>&lt;asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="RecipientID" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."&gt;
          &lt;Columns&gt;
              &lt;asp:BoundField DataField="RecipientID" HeaderText="Recipient ID" ReadOnly="True" SortExpression="RecipientID" /&gt;
              &lt;asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName" /&gt;
              &lt;asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName" /&gt;
              &lt;asp:BoundField DataField="City" HeaderText="City" SortExpression="City" /&gt;
              &lt;asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" /&gt;
              &lt;asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /&gt;
              &lt;asp:BoundField DataField="MailingLists" HeaderText="Mailing Lists" SortExpression="MailingLists" /&gt;
          &lt;/Columns&gt;
      &lt;/asp:GridView&gt;
      &lt;asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="&lt;%$ ConnectionStrings:NewsletterConnectionString1 %&gt;" DeleteCommand="DELETE FROM [Recipients] WHERE [RecipientID] = @RecipientID" InsertCommand="INSERT INTO [Recipients] ([FirstName], [LastName], [City], [Email], [Phone]) VALUES (@FirstName, @LastName, @City, @Email, @Phone)" ProviderName="&lt;%$ ConnectionStrings:NewsletterConnectionString1.ProviderName %&gt;" SelectCommand="SELECT [RecipientID], [FirstName], [LastName], [City], [Email], [Phone] FROM [Recipients]" UpdateCommand="UPDATE [Recipients] SET [FirstName] = @FirstName, [LastName] = @LastName, [City] = @City, [Email] = @Email, [Phone] = @Phone WHERE [RecipientID] = @RecipientID"&gt;
          &lt;DeleteParameters&gt;
              &lt;asp:Parameter Name="RecipientID" Type="Int32" /&gt;
          &lt;/DeleteParameters&gt;
          &lt;InsertParameters&gt;
              &lt;asp:Parameter Name="FirstName" Type="String" /&gt;
              &lt;asp:Parameter Name="LastName" Type="String" /&gt;
              &lt;asp:Parameter Name="City" Type="String" /&gt;
              &lt;asp:Parameter Name="Email" Type="String" /&gt;
              &lt;asp:Parameter Name="Phone" Type="String" /&gt;
          &lt;/InsertParameters&gt;
          &lt;UpdateParameters&gt;
              &lt;asp:Parameter Name="FirstName" Type="String" /&gt;
              &lt;asp:Parameter Name="LastName" Type="String" /&gt;
              &lt;asp:Parameter Name="City" Type="String" /&gt;
              &lt;asp:Parameter Name="Email" Type="String" /&gt;
              &lt;asp:Parameter Name="Phone" Type="String" /&gt;
              &lt;asp:Parameter Name="RecipientID" Type="Int32" /&gt;
          &lt;/UpdateParameters&gt;
      &lt;/asp:SqlDataSource&gt;
  </code></pre>
  
  <p>How can I create a <code>GridView</code> (or possible ListView) containing all the information from those three tables (recipient's details with mailing lists' names)?</p>
  
  <p>UPDATE:
  I have such a query to use as data-source:</p>
  
  <pre><code>SELECT Recipients.RecipientID, Recipients.FirstName, Recipients.LastName, 
  Recipients.City, Recipients.Email, Recipients.Phone, MailingLists.Name
  FROM Recipients
  INNER JOIN RecipientMailingList
  ON Recipients.RecipientID = RecipientMailingList.Recipient_RecipientID
  INNER JOIN MailingLists ON RecipientMailingList.MailingList_MailingListID = MailingLists.MailingListID
  </code></pre>
  
  <p>However, what I get is a different row for the same user with multiple mailing lists. The result I expect is to have one row for each user with a column of comma-separated <code>MailingLists</code>.</p>