RE: Re: UKHA Classified update - SQL Help!

["BUTLER, Tony, FM" <tony.butler@xxxxxxx>]

If you point me to the example you're working from, I'll give it a go.

I presume it's a 1:many and 1:1 relationship respectively between
tblProductsType and tblProducts, and tblProducts and tblStatus
Also, am I right in thinking there's 1:many between tblProductType and
itself, which could drill down infinitely?
Or do you practically see a limit of 2 or 3 deep maybe?
How do you identify a top level parent? ParentProductTypeID=ProductTypeID
or
ParentProductTypeID= some special value (eg -1) ?

If you were extracting as XML of course, it would be a simple matter to
xform it with XSLT and generate the counts for you.....

Tony
> -----Original Message-----
> From: graham@xxxxxxx [mailto:graham@xxxxxxx]
> Sent: 27 March 2002 09:45
> To: ukha_d@xxxxxxx
> Subject: [ukha_d] Re: UKHA Classified update - SQL Help!
>
>
>
> Jon Whiten writes:
>
> > I _hate_ having to cut paste data from a web browser to
> excel - in fact one
> > of our customers attempted to convince us that this is a
> viable way of
> > receiving orders.  It is quite simple to send (using asp) a
> page to excel
> > assuming the client has both Excel and IE.
> >
> Fair comment Jon, and I suppose it is safe to assume that
> everyone will
> have access to both IE and Excel (after all the whole has
> Microsoft don't
> they ;-)). I will put a page up with this functionality as
> soon as I get a
> chance.
>
> On a related topic, I was trying to write an SQL Server
> stored procedure to
> drill down a 'branch' in the product hierarchy and calculate the total
> number of products attached to all the branches below. The
> stored procedure
> runs without errors, is based on the example in the 'Bokks online' but
> doesn't appear to give any result! I don't have access to the
> code at the
> moment but if there are any SQL/ASP gurus out there who fancy
> a challenge,
> here are the table structures:
>
> tblProductsType
> ProductTypeID int
> Description varchar (30)
> ParentProductTypeID int
> DeletedFlag int
>
> tblProducts
> ProductID int
> Description varchar (100)
> ProductTypeID int
> StatusID int
>
> tblStatus
> StatusID int
> Description varchar (30)
> ArchivedFlag int
> DeletedFlag int
> PublishedFlag int
>
> There are other fields on these tables, but they don't matter
> for now. In
> plain English, what I require is a stored procedure that will take a
> ProductTypeID as input and the deliver a count of all Products, with
> PublishedFlag set to 1 in the Status table, across all lower level
> ProductTypes that have DeletedFlag set to 0.
>
> If anyone can help and you want more info then let me know.
>
> Regards
>
> Graham
>
> ------------------------ Yahoo! Groups Sponsor
> ---------------------~-->
> FIND OLD SCHOOL FRIENDS and OLD FLAMES
> Click here to start your search at Reunion.com today!
> http://us.click.yahoo.com/NFsLKA/Dn2DAA/ySSFAA/IBOolB/TM
> --------------------------------------------------------------
> -------~->
>
> For more information: http://www.automatedhome.co.uk
> Post message: ukha_d@xxxxxxx
> Subscribe:  ukha_d-subscribe@xxxxxxx
> Unsubscribe:  ukha_d-unsubscribe@xxxxxxx
> List owner:  ukha_d-owner@xxxxxxx
>
> Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/
>
>


********************************************************************
Visit our Internet site at http://www.rbsmarkets.com

This e-mail is intended only for the addressee named above.
As this e-mail may contain confidential or privileged information,
if you are not the named addressee, you are not authorised to
retain, read, copy or disseminate this message or any part of it.
********************************************************************