how to select products by category | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

how to select products by category

Hi, I need your help, please!
I my aspx page I want to retrieve information from my sql server database and I don#%92t know how to select products by category
I want it to be something like that: Category 1
Product 1
Product 2
Product 3
Product 4 Category 2
Product 1
Product 2 Category 3
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6 Thanks in advance
you can use the Group by clause to do that Pls go through the BOL:
Search for the keyword "GROUP BY clause" Check the example like: USE pubs
SELECT type, AVG(price)
FROM titles
WHERE royalty = 10
GROUP BY type -Johnson

This is my table.
I don’ t know how could i use group by in my case. Classe_nameSub_classes
Industrial ProductsHVAC Piping
Industrial ProductsMetal Surface Cleaners
Industrial ProductsHand Soap & Hand Cleaners
Industrial ProductsPlant Cleaners & Sanitizers
Industrial ProductsDisinfectant Cleaners
Industrial ProductsDegreasing & Pressure Washing
Industrial ProductsCar & Truck Washing
Industrial ProductsAll Purpose Cleaners
Janitorial and maintenaceDisinfectant Cleaners
Janitorial and maintenaceCarpet Care
Janitorial and maintenaceBathroom & Tile Cleaners
Water treatmentCustom made products
Water treatmentIron Based Coagulants
Water treatmentAluminum Based Coagulants
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by joanne</i><br /><br />Hi,<br />This is my table.<br />I don’ t know how could i use group by in my case.<br />Classe_nameSub_classes<br />Industrial ProductsHVAC Piping<br />Industrial ProductsMetal Surface Cleaners<br />Industrial ProductsHand Soap & Hand Cleaners<br />Industrial ProductsPlant Cleaners & Sanitizers<br />Industrial ProductsDisinfectant Cleaners<br />Industrial ProductsDegreasing & Pressure Washing<br />Industrial ProductsCar & Truck Washing<br />Industrial ProductsAll Purpose Cleaners<br />Janitorial and maintenaceDisinfectant Cleaners<br />Janitorial and maintenaceCarpet Care<br />Janitorial and maintenaceBathroom & Tile Cleaners<br />Water treatmentCustom made products<br />Water treatmentIron Based Coagulants<br />Water treatmentAluminum Based Coagulants<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Hi is this table or data in table.<br /><br />Please post some sample data with table schema and the result you wanted.<br /><br />Because data you posted seems the result you require and data in the form in table.<br />Then you dont need to use group by or any.<br /><br />Simply fire select col1,col2 from yourtable<br />As the select will call all rows with category.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
Something like Select Product from yourTable group by Category Madhivanan Failing to plan is Planning to fail
Hi, <br />I will tray to explain again.<br />I want to send in my web page information from the database.<br />With my code, I see the category for every product but I what to see just one time the category for all products.<br /><br />This is my code:<br /><br /> Sub Page_Load(Sender As Object, E As EventArgs)<br /> dim dbread<br />Dim MyConnection<br /> MyConnection = New SqlConnection("server=royal;database=aqua;UID=sa<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />assword=xxxx")<br />MyConnection.Open()<br /> If Not (Page.IsPostBack)<br />Dim DS As DataSet = New DataSet()<br /><br />Dim myCommand as New OleDbCommand ("select * from Products order by Classe_name,Sub_classes ", MyConnection)<br />Dim myDataAdapter as New OleDbDataAdapter(myCommand)<br /><br />myDataAdapter.Fill(DS, "Products")<br /> MyDataList.DataSource = DS.Tables("Products").DefaultView<br /> MyDataList.DataBind()<br /> End If<br /> End Sub<br />&lt;/script&gt;<br /><br />&lt;body topmargin="0" leftmargin="0" marginwidth="0" marginheight="0"&gt;<br /><br /><br /> &lt;ASP<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />ataList id="MyDataList" RepeatColumns="3" RepeatDirection="Horizontal" runat="server"&gt;<br /><br /> &lt;ItemTemplate&gt;<br /><br /> &lt;div style="padding:15,15,15,15;font-size:10pt;font-family:Verdana"&gt;<br /><br /> &lt;div style="font:12pt verdana;color<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />arkred"&gt;<br /> &lt;i&gt;&lt;b&gt;&lt;%# DataBinder.Eval(Container.DataItem, "Classe_name") %&gt;&lt;/i&gt;&lt;/b&gt;<br /> &lt;/div&gt;<br /><br /> &lt;br&gt;<br /><br /> &lt;b&gt;Subclasses: &lt;/b&gt;&lt;%# DataBinder.Eval(Container.DataItem, "Sub_classes") %&gt;&lt;br&gt;<br /><br /> &lt;/div&gt;<br /><br /> &lt;/ItemTemplate&gt;<br /><br /> &lt;/ASP<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />ataList&gt;<br /><br />And this is my web page:<br /><br /><br />Industrial Products <br />Subclasses: All Purpose Cleaners<br /><br />Industrial Products <br />Subclasses: Disinfectant Cleaners<br /><br />Industrial Products <br />Subclasses: Metal Surface Cleaners<br /><br />Janitorial and maintenace <br />Subclasses: Carpet Care<br /><br />Janitorial and maintenace <br />Subclasses: Disinfectant<br /><br />Janitorial and maintenace <br />Subclasses: Bathroom & Tile Cleaners<br /><br />Water treatment <br />Subclasses: Custom made products<br /><br />For example, I want to see Industrial Product oen time and all products for this,one time janitorial and all product for this and so one.<br />Thanks very much and sorry for my english<br />
1. Do you know how to use XML? In other words if I could show you how to return the data in an XML stream that would be exactly the way you want it would that help you out? Like: &lt;Category description="Category 1"&gt;&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roducts description="Other sub class"/&gt;&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roducts description="Some sub class"/&gt;&lt;/Category&gt;&lt;Category description="Category 2"&gt;&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />roducts description="Some sub class for category 2"/&gt;&lt;/Category&gt;<br /><br />2. I don’t know much about web coding, but I have seen a little and I’m not sure how you expect to see the following using a single repeater:<br />Category 1<br />Product 1<br />Product 2<br />Product 3<br /><br />Category 2<br />Product 5<br />Product 6<br /><br />I think you would need to setup a template that would be Category with list of products as the style, and then repeat that. Or modify your web form such that you bring back your data in a dataset and decide how to show it on the screen. You can easily select the data as:<br /><br />select Category.Description, Products.Description<br />from Category INNER JOIN Products on Category.ID = Products.CatID<br />order by Category.Description, Products.Description