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.

Answer 1

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,

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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,

Answer 6

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.

Answer 7

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.

Answer 8

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 .

Answer 9

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!!!!

Answer 10

You Try and then tell me :)

Anyways, I found alote comfort with this new version.

Answer 11

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