locked
unable to join tables RRS feed

  • Question

  • User-305388250 posted

    Hey,

    I was wondering if you could help me. You have given some excellent reponses to peoples problems so i think you may be able to help..

    Basically i am trying to join 2 tables and output some information only if tbl_customer_bookings.county = tbl_companies.county_name.

    However i am having problems doing this. The output should give me 3 returns "andrew", "john", and "vgv". It DOES do this, BUT repeats it four times for each person. Below is the result i get:-

    Customer Name Mobile No County
    John ouhn Cheshire
    vgv hv Cheshire
    andrew 123455679 Cheshire
    John ouhn Cheshire
    vgv hv Cheshire
    andrew 123455679 Cheshire
    John ouhn Cheshire
    vgv hv Cheshire
    andrew 123455679 Cheshire
    John ouhn Cheshire
    vgv hv Cheshire
    andrew 123455679 Cheshire

    I have checked for duplicate data in the database but i only have 3 fields so why is this repeating so many times..?

    The code i have used for my gridview is shown below:-

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

    BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px"

    CellPadding="4" DataSourceID="AccessDataSource1" Width="505px">

    <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />

    <RowStyle BackColor="White" ForeColor="#330099" />

    <Columns>

    <asp:BoundField DataField="county_name" HeaderText="county_name"

    SortExpression="county_name" Visible="False" />

    <asp:BoundField DataField="cust_id" HeaderText="cust_id" InsertVisible="False"

    SortExpression="cust_id" Visible="False" />

    <asp:BoundField DataField="cust_name" HeaderText="Customer Name"

    SortExpression="cust_name" />

    <asp:BoundField DataField="mobile_no" HeaderText="Mobile No"

    SortExpression="mobile_no" />

    <asp:BoundField DataField="county" HeaderText="County"

    SortExpression="county" />

    </Columns>

    <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />

    <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />

    <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />

    </asp:GridView>

    <asp:AccessDataSource ID="AccessDataSource1" runat="server"

    DataFile="~/App_Data/test.mdb"

    SelectCommand="SELECT tbl_companies.county_name, tbl_customer_bookings.cust_id, tbl_customer_bookings.cust_name, tbl_customer_bookings.mobile_no, tbl_customer_bookings.county FROM (tbl_customer_bookings INNER JOIN tbl_companies ON tbl_companies.county_name = tbl_customer_bookings.county)">

    </asp:AccessDataSource>

    Please Help

    Thanks

    Friday, March 6, 2009 10:32 PM

Answers

  • User77042963 posted

    Try to modify your SELECT statement to this:

    SelectCommand="SELECT DISTINCT  tbl_companies.county_name, tbl_customer_bookings.cust_id, tbl_customer_bookings.cust_name, tbl_customer_bookings.mobile_no, tbl_customer_bookings.county FROM  tbl_customer_bookings INNER JOIN tbl_companies ON tbl_companies.county_name = tbl_customer_bookings.county"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 6, 2009 11:22 PM

All replies

  • User77042963 posted

    Try to modify your SELECT statement to this:

    SelectCommand="SELECT DISTINCT  tbl_companies.county_name, tbl_customer_bookings.cust_id, tbl_customer_bookings.cust_name, tbl_customer_bookings.mobile_no, tbl_customer_bookings.county FROM  tbl_customer_bookings INNER JOIN tbl_companies ON tbl_companies.county_name = tbl_customer_bookings.county"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 6, 2009 11:22 PM
  • User-305388250 posted

    Thank you so much, that works now.

    Would it be ok to ask you for help if i get stuck again?

    Thanks again.

    Regards

    Saturday, March 7, 2009 8:14 AM