By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

How do i phrase this simple query

100+
P: 375
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
Jun 21 '09 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
What do you have so far?


--- CK
Jun 21 '09 #2

100+
P: 375
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
Jun 22 '09 #3

code green
Expert 100+
P: 1,726
Expand|Select|Wrap|Line Numbers
  1. select distinct top 
  2. CAST(@count id AS VARCHAR(4) )
  3. from es_category)order by es_product.categoryid
or use ROWCOUNT
Jun 22 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.