How do i phrase this simple query

[cmrhema]

Hi,

I need help in phrasing this simple query.
I have two tables
1. Category table, it consists of categoryid and productid
2. product table, it consists of categoryid, productid, productname, etc.

Now I need to display 3 categories with 4 products in each category.
Its not necessary that all categories will have 4 products, so first i want to find if there are 4 products in that particular category , if yes then display it.


Further I want to input the number of categories and products from user end.
eg. if the user asks 1 category and 2 products of the same category i should be able to display that
or he may ask 5 categorys and all products.

Can anyone help please
Regards
cmrhema

[ck9663]

What do you have so far?


--- CK

[cmrhema]

Ok I am providing you with sample data

Category Id CategoryName
1 Gifts
2 Sports
3 Caskets
4 Cards



Product Id CategoryId ProductName
3 1 Bag
4 1 Camera 3D
5 1 MousePad
10 1 LampShade
11 1 NamePlate
12 1 Box
6 2 Apron
7 2 Baby
8 2 Cup
103 3 Casket Spray with Fabric Drape
104 3 Full Cover Hanging Casket Spray
105 3 Green Casket Spray with Apple
106 3 Infant Casket Spray with Daisies
107 3 Interior Casket Lid Arrangement

The user will input 3,2(ie, 3 categories with 2 products each), the output should be
3 1 Bag
4 1 Camera 3D
6 2 Apron
7 2 Baby
103 3 Casket Spray with Fabric Drape
104 3 Full Cover Hanging Casket Spray


If he gives 2,4(ie. 2 categories with 3 products each) the output should be as follows, it should not take the category number 2 as it has only 2 products

3 1 Bag
4 1 Camera 3D
5 1 MousePad
10 1 LampShade
103 3 Casket Spray with Fabric Drape
104 3 Full Cover Hanging Casket Spray
105 3 Green Casket Spray with Apple
106 3 Infant Casket Spray with Daisies

I tried this way, now only one restriction, I want to pass the value "3", in "select distinct top 3" as a parameter, thro stored procedure,


select * from es_product where es_product.categoryid in
(select distinct top 3 id from es_category)order by es_product.categoryid



in the stored procedure when I write

declare @count as int

set @count=3

select * from es_product where es_product.categoryid in
(select distinct top @count id from es_category)order by es_product.categoryid

it does not accept
what the variable count
What should I do?

Regards
cmrhema

[code green]

select distinct top 
CAST(@count id AS VARCHAR(4) )
from es_category)order by es_product.categoryid

or use ROWCOUNT