How to get this output?

--____HTKHYJYPOBWXJCTZNNWX____
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

OK, I have a tool I've developed but now I have a challenge that I need to =
overcome.=20
I want the desired query output:

Home
About Us
History
Founders
Pictures
Contact Form
Services
Inspection
Approval=20
Residential
Commercial

=20
Category
CategoryID - IDentity
CategoryTitle - varchar(50)
CategoryDescription -varchar(500)

Sample Data
1,Home,'n/a'
2,About Us, 'n/a'
3,Contact Us,'na'
4,Services,'n/a'


Content
ContentID
CategoryID
ContentTitle
Content

Sample Data
1,2,History,'bla, bla, bla'
2,2,Founders,'bla, bla, bla'
3,2,Pictures,'bla, bla, bla'
4,4,Inspection,'bla, bla, bla'
5,4,Approval,'bla, bla, bla'
6,4,Residential,'bla, bla, bla'
7,4,Commercial,'bla, bla, bla'

--____HTKHYJYPOBWXJCTZNNWX____
Content-Type: multipart/related; boundary="____XWYVBHJSMQXRXICAQSSN____"


--____XWYVBHJSMQXRXICAQSSN____
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable


>

Re: How to get this output?

Shawn Ferguson wrote:
> OK, I have a tool I've developed but now I have a challenge that I
> need to overcome.
> I want the desired query output:
>
> Home
> About Us
> History
> Founders
> Pictures
> Contact Form
> Services
> Inspection
> Approval
> Residential
> Commercial
>
>
> Category
> CategoryID - IDentity
> CategoryTitle - varchar(50)
> CategoryDescription -varchar(500)
>
What database type and version please? SQL Server?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: How to get this output?

--____AXZIXLTCPFGFSUNMCVMY____
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Apologies, the database is SQL Server 2000 Enterprise Version. I guess =
the key is showing the categories that have no content associated with =
them. Also forgot the datatypes of Content:

ContentID - Identity
CategoryID - int foreign key (Category.CategoryID)
ContentTitle - varchar(50)
Content - varchar(1000)

Thanks in advance!

>>> Bob Barrows [MVP] 12/29/05 9:48 AM >>>

Shawn Ferguson wrote:
> OK, I have a tool I've developed but now I have a challenge that I
> need to overcome.
> I want the desired query output:
>
> Home
> About Us
> History
> Founders
> Pictures
> Contact Form
> Services
> Inspection
> Approval
> Residential
> Commercial
>
>
> Category
> CategoryID - IDentity
> CategoryTitle - varchar(50)
> CategoryDescription -varchar(500)
>
What database type and version please? SQL Server?

Bob Barrows
--=20
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

--____AXZIXLTCPFGFSUNMCVMY____
Content-Type: multipart/related; boundary="____OEMWUPAFPJLUVEGSKSLQ____"


--____OEMWUPAFPJLUVEGSKSLQ____
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable


>

Re: How to get this output?

Shawn Ferguson wrote:
> Apologies, the database is SQL Server 2000 Enterprise Version. I
> guess the key is showing the categories that have no content
> associated with them. Also forgot the datatypes of Content:
>
> ContentID - Identity
> CategoryID - int foreign key (Category.CategoryID)
> ContentTitle - varchar(50)
> Content - varchar(1000)
>
> Thanks in advance!
>

So are you just asking how to create a query that will retrieve the data you
need? If so, I would use a union query encapsulated in a stored procedure:

CREATE PROCEDURE CategoryTree AS
SELECT 'Category' as Type,
CategoryID,
CategoryTitle as Display,
0 as [ContentID]
FROM Category
UNION ALL
SELECT 'Content', t1.CategoryID, ContentTitle,ContentID
FROM Category t1 LEFT JOIN Content t2
ON t1.CategoryID=t2.CategoryID
ORDER BY CategoryID,ContentID

Here is how I would handle the results of this procedure:

<%
dim cn, rs, sql, arData, arDivs, div
set cn=createobject("adodb.connection")

cn.open "provider=sqloledb; data source=yourserver;" & _
"user id=login; password=yourpassword;" & _
"Initial Catalog = yourdatabase"

set rs=createobject("adodb.recordset")
cn.CategoryTree rs
if not rs.eof then arData=rs.getrows
rs.close:set rs = nothing
cn.close: set cn=nothing
if isarray(arData) then
redim arDivs(ubound(arData,2))
for i=0 to ubound(arData,2)
div = "

Re: How to get this output?

Bob Barrows [MVP] wrote:
> dim cn, rs, sql, arData, arDivs, div

Here is a quicker way, using getstring instead of getrows:

dim cn, rs, sHTML
set cn=createobject("adodb.connection")
cn.Open "provider=sqloledb; ..."
set rs=createobject("adodb.recordset")
cn.CategoryTree rs
if not rs.eof then 'arData=rs.getrows
sHTML=rs.GetString(adClipString,,""">","" & _
vbcrlf & "

Re: How to get this output?

--____JDKZPZVDEHPRUCNIXEQQ____
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Thank you. I have received 2 extra records (content,1,NULL, NULL|content,3=
,NULL,NULL), how could those be eliminated. Secondly, what if a duplicate =
content and category record was thrown in the mix, how could we ensure =
duplicates are eliminated?

Thanks a million!

>>> Bob Barrows [MVP] 12/29/05 11:46 AM >>>

Bob Barrows [MVP] wrote:
> dim cn, rs, sql, arData, arDivs, div

Here is a quicker way, using getstring instead of getrows:

dim cn, rs, sHTML
set cn=3Dcreateobject("adodb.connection")
cn.Open "provider=3Dsqloledb; ..."
set rs=3Dcreateobject("adodb.recordset")
cn.CategoryTree rs
if not rs.eof then 'arData=3Drs.getrows
sHTML=3Drs.GetString(adClipString,,""">","" & _
vbcrlf & "

Re: How to get this output?

Those can be eliminated by getting rid of the unnecessary left join. Here is
a revision to make thee examples I posted work:

CREATE PROCEDURE CategoryTree AS
SELECT Type,Display FROM (
SELECT 'Category' as Type,
CategoryID,
CategoryTitle as Display,
0 as [ContentID]
FROM Category
UNION ALL
SELECT 'Content', CategoryID, ContentTitle,ContentID
FROM Category) as q
ORDER BY CategoryID,ContentID

As for duplicates, why would they exist? Don't you have unique indexes on
these tables to prevent duplicates? Given that the database is
poorly-designed and you can't correct it for some reason, just add the
DISTINCT keyword to the outer slect statement:

CREATE PROCEDURE CategoryTree AS
SELECT DISTINCT Type,Display FROM (
SELECT 'Category' as Type,
CategoryID,
CategoryTitle as Display,
0 as [ContentID]
FROM Category
UNION ALL
SELECT 'Content', CategoryID, ContentTitle,ContentID
FROM Category) as q
ORDER BY CategoryID,ContentID

Shawn Ferguson wrote:
> Thank you. I have received 2 extra records (content,1,NULL,
> NULL|content,3,NULL,NULL), how could those be eliminated. Secondly,
> what if a duplicate content and category record was thrown in the
> mix, how could we ensure duplicates are eliminated?
>
> Thanks a million!
>
>>>> Bob Barrows [MVP] 12/29/05 11:46 AM >>>
>
> Bob Barrows [MVP] wrote:
>> dim cn, rs, sql, arData, arDivs, div
>
> Here is a quicker way, using getstring instead of getrows:
>
> dim cn, rs, sHTML
> set cn=createobject("adodb.connection")
> cn.Open "provider=sqloledb; ..."
> set rs=createobject("adodb.recordset")
> cn.CategoryTree rs
> if not rs.eof then 'arData=rs.getrows
> sHTML=rs.GetString(adClipString,,""">","" & _
> vbcrlf & "