none
Avoid Repetition of primary tables's record

    Question

  • hi all,
    can you tell me how to avoid repetition of main records when one main(Primary table) record have more than one child(secondry table) records. I am using DataList to show records.
    For example I want to show the following
    Tools & Equipment
    http://www.mobmasti.com
    MobMasti
    Mobile Portal
    Manufactures
    http://www.manu.com
    Manufacturer
    desc manufactur
    Rental Companies
    http://www.rentit.com
    Rent It
    rent it desc
    Buy & Sell
    http://www.mobmasti2.com
    MT
    desc
    Manufactures
    http://www.allofuse.com
    all stff
    desc
    Employment
    http://www.mobmasti.com/
    MobMasti
    Mobile Portal
    AS(I want to show in the following way)
    Tools & Equipment
    http://www.mobmasti.com
    MobMasti
    Mobile Portal
    Manufactures
    http://www.manu.com
    Manufacturer
    desc manufactur

    http://www.allofuse.com 
    all stff 
    desc
    Rental Companies
    http://www.rentit.com
    Rent It
    rent it desc
    Buy & Sell
    http://www.mobmasti2.com
    MT
    desc
    Employment
    http://www.mobmasti.com/
    MobMasti
    Mobile Portal

    My query is as follows
    SELECT     DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                          '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
    FROM         DirectoryMainCat INNER JOIN
                          DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
    WHERE DirectoryLinks.Hitcount > 0
    ORDER BY DirectoryLinks.HitCount DESC

    where 'DirectoryMainCat' is the main table and 'DirectoryLinks' is the child table.

    thanks in advance.
    Monday, December 05, 2005 12:03 PM

All replies

  • I am still not sure what is your quesion. where is you primary key repetition ? in the actual Parent (main) table or do you have in your database or  in the data View of the DataList?

    If its In your database and its .mdb then you have to change your Primary key "index" property to "Yes (No Duplicates)".

    If its in DataList then ofcourse it depends on how did you do query on your database and how did you creat relations.



    regards,
    Monday, December 05, 2005 12:48 PM
  • Omar Kamal, first of all thanks for your reply.
    There is nothing wroing in my tables.
    i am using SQL Server 2000.
    My main table is 'DirectorymainCat' and Child table is 'DirectoryLinks'
    'CategoryId' is the Primary Key in main table and also being used as foreign key in 'DirectoryLinks' table
    When I run the query

    SELECT     DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                          '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
    FROM         DirectoryMainCat INNER JOIN
                          DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
    WHERE DirectoryLinks.Hitcount > 0
    ORDER BY DirectoryLinks.HitCount DESC

    the results that are coming are absolutely right.

    Now my problem is how I avoid the repetition of a record that is coming from primary table (DirectoryMainCat) when it has one than one child records in the child table (DirectoryLinks).

    Certaily, I have to control it when binding DataList so that if a primary record(primary table's record) has more than one related records, the primary record should display only once and all it's child records should display under it and then the next primary record and it's child records and so on.
    I don't know how to do it.
    please reply soon,
    thanks

    Monday, December 05, 2005 1:12 PM
  • If you only want to have a single instance of the primary table then adding the DISTINCT keyword after the first SELECT should work (if im interpreting your request correctly, but i think ive had too much coffee today :))

    Try something like:
    SELECT DISTINCT    DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                          '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
    FROM         DirectoryMainCat INNER JOIN
                          DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
    WHERE DirectoryLinks.Hitcount > 0
    ORDER BY DirectoryLinks.HitCount DESC

    Im not sure whether this is what you are asking but i hope it helps

    Monday, December 05, 2005 5:12 PM
  • thanks to all who replied to my query.
    but i have not yet got the solution to my problem.
    I have to run this query

    SELECT DISTINCT    DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                          '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
    FROM         DirectoryMainCat INNER JOIN
                          DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
    WHERE DirectoryLinks.Hitcount > 0
    ORDER BY DirectoryLinks.HitCount DESC


    and the result it produces should be controled through code.
    which should loook like:

    Tools & Equipment

    http://www.mobmasti.com
     
    MobMasti
    Mobile Portal
     
    Manufactures

    http://www.manu.com
     
    it's me manufactur
    desc manufactur
     
    http://www.allofuse.com
     
    all stff
    desc

    Rental Companies

    http://www.rentit.com
     
    Rent It
    rent it desc
     
    Buy & Sell

    http://www.mobmasti2.com
     
    MT
    desc

    http://www.itc.com
     
    B and S dec 6
    this is b and s on dec 6


    Employment

    http://www.mobmasti.com/
     
    MobMasti
    Mobile Portal
     
     
     


    not like this:
    Tools & Equipment

    http://www.mobmasti.com
     
    MobMasti
    Mobile Portal
     
    Manufactures

    http://www.manu.com
     
    Manufacturer
    desc manufactur
     
    Rental Companies

    http://www.rentit.com
     
    Rent It
    rent it desc
     
    Buy & Sell

    http://www.mobmasti2.com
     
    MT
    desc
     
    Manufactures

    http://www.allofuse.com
     
    all stff
    desc
     
    Buy & Sell

    http:/
     
    B and S dec 6
    this is b and s on dec 6
     
    Employment

    http://www.mobmasti.com/
     
    MobMasti
    Mobile Portal
     
     
      where text in bold is main category name(it is coming from DirectoryMainCat table).
    and rest of the data(under each main category) is coming from DirectoryLinks table(child table).
    please reply soon!!!
    thanks

    Tuesday, December 06, 2005 9:20 AM
  • Dear Aamir Iqbal,

    Its not all about how you do query but actually u have to see also that how to bind the query with your control.

    Binding have two ways one is "Tabuler return binding" and other is "Scalar(single) Value binding". One returns table and the other a single value.  I dont know what control u have for binding ur this qurey and how are u binding it?

    please, send the code which works as binding for your control. Then we can see where is the actual problem.

    Regards,
    Tuesday, December 06, 2005 11:20 AM
  • hello,omar.
    thanks for your reply for the second time.
    i am sending the code for binding
    binding code is as simple as follows
    -------------------------------------
    this.DataList1.DataSource=ds.Tables[0].DefaultView;
    this.DataList1.DataBind();

    -------------------------------------

    Html code for the DataList is as follows:
    -------------------------------------
    <asp:datalist id="DataList1" runat="server">
           <HeaderTemplate>
            <asp:Label id="Label4" runat="server" ForeColor="#0000C0" Font-Italic="True">Popular Sites</asp:Label>
           </HeaderTemplate>
           <ItemTemplate>
            <TABLE id="Table2" cellSpacing="0" cellPadding="0" width="100%" border="0">
             <TR>
              <TD>
               <P>
                <asp:Label id=Label1 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.CategoryName") %>' Font-Bold="True">
                </asp:Label></P>
               <P>
                <asp:Label id=Label2 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.url") %>'>
                </asp:Label></P>
              </TD>
             </TR>
             <TR>
              <TD>
               <asp:Label id=Label3 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.title") %>'>
               </asp:Label></TD>
             </TR>
             <TR>
              <TD>
               <asp:Label id=Label5 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.Description") %>'>
               </asp:Label></TD>
             </TR>
            </TABLE>
           </ItemTemplate>
          </asp:datalist>
    --------------------------------------------
    and I write the query again.
    ------------------------------------------
    SELECT     DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                          '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
    FROM         DirectoryMainCat INNER JOIN
                          DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
    WHERE DirectoryLinks.Hitcount > 0
    ORDER BY DirectoryLinks.HitCount DESC
    --------------------------------------------
    waiting for your reply.......
    thanks again.

    Tuesday, December 06, 2005 11:35 AM
  • hello,omar.
    thanks for your reply for the second time.
    i am sending the code for binding
    binding code is as simple as follows
    -------------------------------------
    this.DataList1.DataSource=ds.Tables[0].DefaultView;
    this.DataList1.DataBind();

    -------------------------------------

    Html code for the DataList is as follows:
    -------------------------------------
    <asp:datalist id="DataList1" runat="server">
           <HeaderTemplate>
            <asp:Label id="Label4" runat="server" ForeColor="#0000C0" Font-Italic="True">Popular Sites</asp:Label>
           </HeaderTemplate>
           <ItemTemplate>
            <TABLE id="Table2" cellSpacing="0" cellPadding="0" width="100%" border="0">
             <TR>
              <TD>
               <P>
                <asp:Label id=Label1 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.CategoryName") %>' Font-Bold="True">
                </asp:Label></P>
               <P>
                <asp:Label id=Label2 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.url") %>'>
                </asp:Label></P>
              </TD>
             </TR>
             <TR>
              <TD>
               <asp:Label id=Label3 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.title") %>'>
               </asp:Label></TD>
             </TR>
             <TR>
              <TD>
               <asp:Label id=Label5 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.Description") %>'>
               </asp:Label></TD>
             </TR>
            </TABLE>
           </ItemTemplate>
          </asp:datalist>
    --------------------------------------------
    and I write the query again.
    ------------------------------------------
    SELECT     DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                          '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
    FROM         DirectoryMainCat INNER JOIN
                          DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
    WHERE DirectoryLinks.Hitcount > 0
    ORDER BY DirectoryLinks.HitCount DESC
    --------------------------------------------
    waiting for your reply.......
    thanks again.

    Tuesday, December 06, 2005 11:38 AM
  • Are you using VWD 2005 or 2003 .NET? If u have older Version then I would suggest download VWD Express 2005 (its free) and build your webpage in there. Its alote easier there and many tools works better.

    see my code I just generated as a test and it works excelent; 



    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="TestForm4DataSet.aspx.cs" Inherits="TestForm4DataSet" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml" >

    <head runat="server">

    <title>Test Page</title>

    </head>

    <body>

    <form id="form1" runat="server">

    <div>

    <asp:DataList ID="DataList1" runat="server" DataSourceID="AccessDataSource1">

    <ItemTemplate>

    ID:

    <asp:Label ID="IDLabel" runat="server" Text='<%# Eval("ID") %>'></asp:Label><br />

    FullTaskName:

    <asp:Label ID="FullTaskNameLabel" runat="server" Text='<%# Eval("FullTaskName") %>'>

    </asp:Label><br />

    Day:

    <asp:Label ID="DayLabel" runat="server" Text='<%# Eval("Day") %>'></asp:Label><br />

    <br />

    </ItemTemplate>

    </asp:DataList>

    <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/TimeTrackerUpdated.mdb"

    SelectCommand="SELECT Activities.ID, Tasks.FullTaskName, Activities.[Day] FROM (Activities INNER JOIN Tasks ON Activities.Task = Tasks.ID)">

    </asp:AccessDataSource>

    </div>

    </form>

    </body>

    </html>

     



    I dont have older version any more on my this Pc. I won't help you with VS 2003 .NET .

    Tuesday, December 06, 2005 12:30 PM
  • Omar, are you sure that I was generate the results in the pattern that I want and will not repeat a main category again and again?

    thanks very much!!!!

    Tuesday, December 06, 2005 12:39 PM
  • You Try and then tell me :)

    Anyways, I found alote comfort with this new version.
    Tuesday, December 06, 2005 12:52 PM
  • ok,but it will take me time to download the new vs2005. after downloading it, i shall try and then tell you.
    I have another problem that I request you to help me in.
    I have page where I want to show the top 10 'popular web site' based on the hits that websites receive from a web user.
    I have the same tables in this scenario.
    How will i do this this. I am using nested DataLists to do this.
    The code to do this is as below:

    -----------------------------
    public
    DataSet DirectoryPopularSites()

    {

    sqlcon=new SqlConnection();

    sqlcon.ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

    sqlcmd=new SqlCommand();

    sqlcmd.Connection=sqlcon;

    sqlcmd.CommandText="DirectoryFillCategoriesPop";

    sqlcmd.CommandType=CommandType.StoredProcedure;

    sqlcon.Open();

    sqladpt=new SqlDataAdapter();

    sqladpt.SelectCommand=sqlcmd;

    ds=new DataSet();

    sqladpt.Fill(ds,"DirectoryMainCat");

    SqlDataAdapter adpt2=new SqlDataAdapter();

    SqlCommand sqlcmd2=new SqlCommand();

    sqlcmd2.Connection=sqlcon;

    sqlcmd2.CommandText="DirectoryPopularSites";

    sqlcmd2.CommandType=CommandType.StoredProcedure;

    adpt2.SelectCommand=sqlcmd2;

    adpt2.Fill(ds,"DirectoryLinks");

    ds.Relations.Add("Relation1",ds.Tables["DirectoryMainCat"].Columns["CategoryId"],ds.Tables["DirectoryLinks"].Columns["CategoryId"]);

    sqlcmd.Dispose();

    sqlcon.Close();

    return ds;

    }

    -----------------------------------------
    the query for 1st stored procedure (DirectoryFillCategoriesPop)is:
    ---------------------------

    select top 10  CategoryId,CategoryName,Description from DirectoryMaincat
    where hits>0
    -----------------------
    and for 2nd sp is as below:
    ------------------------

    SELECT     DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                          '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
    FROM         DirectoryMainCat INNER JOIN
                          DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
    WHERE DirectoryLinks.Hitcount > 0
    ORDER BY DirectoryLinks.HitCount DESC
    --------------------------------------

    and then I use this code to bind the DataList
    ---------------------------

    this.DataList1.DataSource=objD.DirectoryNewSites(Request.QueryString["indate"].ToString());

    this.DataList1.DataBind();
    ---------------------------
    the html code of nested datalists is
    -----------------------------
    <asp:DataList id="DataList1" runat="server" Width="75%" CellPadding="5">
                          <ItemTemplate>
                           <TABLE class="dottedWhiteTable" id="TableList1Contentes" cellSpacing="0" cellPadding="3"
                            width="100%" border="0">
                            <TR>
                             <TD><A class=alink href='DirectoryMain.aspx?catId=<%# DataBinder.Eval(Container, "DataItem.CategoryId") %>'>
                               <asp:Label id=lblMainCat runat="server" CssClass="globalfont" Font-Bold="True" Text='<%# DataBinder.Eval(Container, "DataItem.CategoryName") %>'>
                               </asp:Label></A></TD>
                            </TR>
                            <TR>
                             <TD>
                              <asp:DataList id=childList runat="server" CellPadding="5" Width="300px" datasource='<%# ((DataRowView)Container.DataItem).Row.GetChildRows("Relation1") %>'>
                               <ItemTemplate>
                                <TABLE id="TableListItems" cellSpacing="0" cellPadding="0" width="100%" border="0">
                                 <TR>
                                  <TD>
                                   <a href='DirectoryHitSite.aspx?linkid=<%# DataBinder.Eval(Container.DataItem, "[\"linkid\"]") %>&url=<%# DataBinder.Eval(Container.DataItem, "[\"url\"]") %>' target=blank>
                                    <asp:Label id=lblTitle runat="server" CssClass="globalFont" Text='<%# DataBinder.Eval(Container.DataItem, "[\"Title\"]") %>'>
                                    </asp:Label></a></TD>
                                  <TD></TD>
                                 </TR>
                                 <TR>
                                  <TD>
                                   <asp:Label id=lblDescription runat="server" CssClass="globalFont" Text='<%# DataBinder.Eval(Container.DataItem, "[\"Description\"]") %>'>
                                   </asp:Label></TD>
                                  <TD></TD>
                                 </TR>
                                 <TR>
                                  <TD>
                                   <asp:Label id=lblUrl runat="server" CssClass="globalFont" Text='<%# DataBinder.Eval(Container.DataItem, "[\"url\"]") %>'>
                                   </asp:Label>
                                   <asp:Label id=lblCount runat="server" CssClass="globalFont" Text='<%# DataBinder.Eval(Container.DataItem, "[\"total\"]") %>'>
                                   </asp:Label></TD>
                                 </TR>
                                 <TR>
                                  <td>
                                   <asp:Label id="lblPopular" runat="server" Text="popular" CssClass="globalfont" Font-Bold="True"
                                    ForeColor="#ff6666"></asp:Label></td>
                                 </TR>
                                </TABLE>
                               </ItemTemplate>
                              </asp:DataList></TD>
                            </TR>
                           </TABLE>
                          </ItemTemplate>
                         </asp:DataList></TD>
    ---------------------------------
    plesae help me in doing this job

    thanks

    Tuesday, December 06, 2005 1:08 PM