Can this be done with sql?

I am trying to produce a query that will create a sorted list and I
don't know if it's even possible.
Here's what I aim to have the query return:
Category1
item1 description
item2 description
Category 2
item1 description
item2 description
Here are the two tables.
tblCategory
==========
catID description
1 Category1
2 Category2
tblItem
===========
itemID itemName itemDescription catID[fk]
1 item1 description 1
2 item2 description 1
3 item1 description 2
4 item2 description 2
Thanks in advance,
RanginaldThat should be done on the client side in a report generator program of some
sort. That said, here's one way to get a listing similar to what you're
asking for (there will be two extra columns used for proper ordering of the
rows, but you can eliminate those in your front-end app):
CREATE TABLE #tblCategory (catID INT PRIMARY KEY NOT NULL,
description VARCHAR(20) NOT NULL)
INSERT INTO #tblCategory (catID, description)
VALUES (1, 'Category1')
INSERT INTO #tblCategory (catID, description)
VALUES (2, 'Category2')
CREATE TABLE #tblItem (itemID INT NOT NULL PRIMARY KEY,
itemName VARCHAR(10) NOT NULL,
itemDescription VARCHAR(20) NOT NULL,
catID INT NOT NULL)
INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
VALUES (1, 'item1', 'description', 1)
INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
VALUES (2, 'item2', 'description', 1)
INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
VALUES (3, 'item1', 'description', 2)
INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
VALUES (4, 'item2', 'description', 2)
SELECT 'B' AS RowType, i.itemName, i.itemDescription, i.catID
FROM #tblItem i
UNION
SELECT 'A' AS RowType, c.description, '', c.catID
FROM #tblCategory c
ORDER BY catID, RowType, itemName
DROP TABLE #tblCategory
DROP TABLE #tblItem
"Ranginald" <davidwank@.gmail.com> wrote in message
news:1155610407.063807.318420@.74g2000cwt.googlegroups.com...
>I am trying to produce a query that will create a sorted list and I
> don't know if it's even possible.
> Here's what I aim to have the query return:
> Category1
> item1 description
> item2 description
> Category 2
> item1 description
> item2 description
> Here are the two tables.
> tblCategory
> ==========
> catID description
> 1 Category1
> 2 Category2
> tblItem
> ===========
> itemID itemName itemDescription catID[fk]
> 1 item1 description 1
> 2 item2 description 1
> 3 item1 description 2
> 4 item2 description 2
> Thanks in advance,
> Ranginald
>|||As I'm new to web programming, I never thought to use a report
generator program.
What I am aiming to do is have a category.aspx page where the user has
a drop down "go" list (data bound) which leads them to the category
they chose via an HTML anchor on the same page (hence the SQL I asked
about).
I have no experience with sql server's report generator (although I
have the express 2005 version). Is this a good place to start for the
very basic needs I have, or could you recommend a .net compatible basic
report generator (little cost would be great, too).
Thanks again in advance for all your help!
-Ranginald
Mike C# wrote:[vbcol=seagreen]
> That should be done on the client side in a report generator program of so
me
> sort. That said, here's one way to get a listing similar to what you're
> asking for (there will be two extra columns used for proper ordering of th
e
> rows, but you can eliminate those in your front-end app):
> CREATE TABLE #tblCategory (catID INT PRIMARY KEY NOT NULL,
> description VARCHAR(20) NOT NULL)
> INSERT INTO #tblCategory (catID, description)
> VALUES (1, 'Category1')
> INSERT INTO #tblCategory (catID, description)
> VALUES (2, 'Category2')
> CREATE TABLE #tblItem (itemID INT NOT NULL PRIMARY KEY,
> itemName VARCHAR(10) NOT NULL,
> itemDescription VARCHAR(20) NOT NULL,
> catID INT NOT NULL)
> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
> VALUES (1, 'item1', 'description', 1)
> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
> VALUES (2, 'item2', 'description', 1)
> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
> VALUES (3, 'item1', 'description', 2)
> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
> VALUES (4, 'item2', 'description', 2)
> SELECT 'B' AS RowType, i.itemName, i.itemDescription, i.catID
> FROM #tblItem i
> UNION
> SELECT 'A' AS RowType, c.description, '', c.catID
> FROM #tblCategory c
> ORDER BY catID, RowType, itemName
> DROP TABLE #tblCategory
> DROP TABLE #tblItem
> "Ranginald" <davidwank@.gmail.com> wrote in message
> news:1155610407.063807.318420@.74g2000cwt.googlegroups.com...|||Ranginald wrote:
> As I'm new to web programming, I never thought to use a report
> generator program.
> What I am aiming to do is have a category.aspx page where the user has
> a drop down "go" list (data bound) which leads them to the category
> they chose via an HTML anchor on the same page (hence the SQL I asked
> about).
>
You don't need a report generator to do this. I don't know ASP.NET, so
I'll give you the method for a traditional ASP page, you can adapt from
there.
Your page will issue a query similar to this:
SELECT
tblCategory.Description,
tblItem.ItemName,
tblItem.ItemDescription
FROM tblItem
INNER JOIN tblCategory
ORDER BY tblCategory.Description, tblItem.ItemName
The results will returned to a Recordset object. In your ASP code,
you're going to loop through that Recordset object:
strCategory = ""
While Not oRecordset.EOF
If oRecordset("Description") <> strCategory Then
' Print a new category header
Response.Write(oRecordset("Description"))
strCategory = oRecordset("Description")
End If
' Print current item listing
Response.Write(oRecordset("ItemName"))
Response.Write(oRecordset("ItemDescription"))
oRecordset.MoveNext
Loop
Does that help?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If you're not generating a report, you don't need a report generator;
although the basic concept is the same as the old report-writing with
"breaks". Just SELECT your data (order it by CategoryID and ItemName), then
loop through the data on the client side and build your drop-down list.
Here's an example in ASP.NET (assumes you have a form with a Drop-down list
named DropDownList1):
Dim SqlCon As SqlClient.SqlConnection
Dim SqlCmd As SqlClient.SqlCommand
Dim SqlDR As SqlClient.SqlDataReader
Try
SqlCon = New SqlClient.SqlConnection("SERVER=;INITIAL
CATALOG=;INTEGRATED SECURITY=SSPI;")
SqlCon.Open()
SqlCmd = New SqlClient.SqlCommand("SELECT c.description AS CategoryName,
i.itemName, i.itemDescription " & _
"FROM tblCategory c " & _
"INNER JOIN tblItem i " & _
"ON c.catID = i.catID " & _
"ORDER BY c.description, i.itemName", SqlCon)
SqlDR = SqlCmd.ExecuteReader()
Dim LastCategory As String = ""
While (SqlDR.Read())
Dim CurrentCategory As String = SqlDR("CategoryName").ToString()
Dim CurrentItem As String = "::" & SqlDR("ItemName").ToString()
If (CurrentCategory <> LastCategory) Then
DropDownList1.Items.Add(CurrentCategory)
LastCategory = CurrentCategory
End If
DropDownList1.Items.Add(CurrentItem)
End While
Catch ex As Exception
Throw New Exception("Error: Database Connection Error: " + ex.Message())
Finally
If Not (SqlDR Is Nothing) Then
SqlDR.Close()
End If
If Not (SqlCmd Is Nothing) Then
SqlCmd.Dispose()
End If
If Not (SqlCon Is Nothing) Then
SqlCon.Dispose()
End If
End Try
"Ranginald" <davidwank@.gmail.com> wrote in message
news:1155687276.781480.170340@.i3g2000cwc.googlegroups.com...
> As I'm new to web programming, I never thought to use a report
> generator program.
> What I am aiming to do is have a category.aspx page where the user has
> a drop down "go" list (data bound) which leads them to the category
> they chose via an HTML anchor on the same page (hence the SQL I asked
> about).
> I have no experience with sql server's report generator (although I
> have the express 2005 version). Is this a good place to start for the
> very basic needs I have, or could you recommend a .net compatible basic
> report generator (little cost would be great, too).
> Thanks again in advance for all your help!
> -Ranginald
> Mike C# wrote:
>|||Perfect! Thanks!
Mike C# wrote:[vbcol=seagreen]
> If you're not generating a report, you don't need a report generator;
> although the basic concept is the same as the old report-writing with
> "breaks". Just SELECT your data (order it by CategoryID and ItemName), th
en
> loop through the data on the client side and build your drop-down list.
> Here's an example in ASP.NET (assumes you have a form with a Drop-down lis
t
> named DropDownList1):
> Dim SqlCon As SqlClient.SqlConnection
> Dim SqlCmd As SqlClient.SqlCommand
> Dim SqlDR As SqlClient.SqlDataReader
> Try
> SqlCon = New SqlClient.SqlConnection("SERVER=;INITIAL
> CATALOG=;INTEGRATED SECURITY=SSPI;")
> SqlCon.Open()
> SqlCmd = New SqlClient.SqlCommand("SELECT c.description AS CategoryNam
e,
> i.itemName, i.itemDescription " & _
> "FROM tblCategory c " & _
> "INNER JOIN tblItem i " & _
> "ON c.catID = i.catID " & _
> "ORDER BY c.description, i.itemName", SqlCon)
> SqlDR = SqlCmd.ExecuteReader()
> Dim LastCategory As String = ""
> While (SqlDR.Read())
> Dim CurrentCategory As String = SqlDR("CategoryName").ToString()
> Dim CurrentItem As String = "::" & SqlDR("ItemName").ToString()
> If (CurrentCategory <> LastCategory) Then
> DropDownList1.Items.Add(CurrentCategory)
> LastCategory = CurrentCategory
> End If
> DropDownList1.Items.Add(CurrentItem)
> End While
> Catch ex As Exception
> Throw New Exception("Error: Database Connection Error: " + ex.Message(
))
> Finally
> If Not (SqlDR Is Nothing) Then
> SqlDR.Close()
> End If
> If Not (SqlCmd Is Nothing) Then
> SqlCmd.Dispose()
> End If
> If Not (SqlCon Is Nothing) Then
> SqlCon.Dispose()
> End If
> End Try
>
> "Ranginald" <davidwank@.gmail.com> wrote in message
> news:1155687276.781480.170340@.i3g2000cwc.googlegroups.com...