Help with a Query

Hi,
I have a problem with a query.
Select distinct count(*)
from
Products B1,
categoryProducts B2
where
B1.ProductID = B2.ProductID and
B2.CategoryID in (
SELECT categoryID FROM Categories WHERE
Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE
CategoryID=4)
)
This query finds all of the CategoryID's from the Categories table, whose
base CategoryID is 4. The table structure of Categories is:
Categories
==========
CategoryID int
Name
Linkage varchar(100)
Data:
CategoryID Name Linkage
...
4 Mind. Body & Spirit 4.1
5 Spiritualism 4.1.1
6 Occult 4.2.2
7 Religion 4.2.3
8 Christianity 4.2.3.1
9 Islam 4.2.3.2
I have a book product which has been categorised into three sub-categories,
using a table called CategoryProducts.
Products
========
ProductID int
Title varcahr(250)
CategoryProducts
================
CategoryID int
ProductID int
Products Data:
-----
ProductID Title
1 The Sacred and the Profane
CategoryProducts Data:
-------
CategoryID ProductID
4 1
5 1
7 1
So The Sacred and the Profane has been categorised into 3 categories, all
falling within or under Mind. Body & Spirit. When I execute the query at
the top here, I get 3 results - this is correct. However, I only want 1
item, since they are all the same book. I was hoping the distinct SQL
keyword would work, but it doesnt, and I am lost - help!
jr.
I'm not sure I understand your query as it uses a count. I assume that was a test and you are really using something like:
Select distinct *
from ...
The asterisk will return all fields from BOTH tables which will be three separate records.
Instead, try something like:
Select distinct Products.ProductID, Products.Title
from ...
That should just give you the single record you want.
HTH.|||Hi,
Thanks for your suggestion, it works using the distinct the way yousuggest. I didnt explain myself correctly ealier, as I do needthe count(*). As part of my paging routine, the first step is tofind out how many records are available in the complete query, beforepaging is used, so I can say "Viewing 1 to 10 of 2000 records". So I need to count the contents of the full dataset using the links tothe categories using the materialized path. I managed to drop theProducts table as it wasnt adding anything to calculate the count value.
Select distinct count(B2.ProductID)
from
categoryProducts B2
where
B2.CategoryID in (
SELECT categoryID FROM Categories WHERE
Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE CategoryID=4)
)
The CategoryProducts table does have 3 entries in it, with differentcategory ids but the same product id. I cannot work out why asking toreturn the distinct on the single product id would give only 1 recordrather than 3. Any thoughts?
thanks,
jr.
|||ZLA,
I worked it out - I had a post on the MS boards, and it was highlightedto me that I need to put the distinct inside the count():
Select distinct count(distinct B2.ProductID)
from
categoryProducts B2
where
B2.CategoryID in (
SELECT categoryID FROM Categories WHERE
Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE CategoryID=4)
)
Thanks for your time in looking at this problem.
jr.