Bob Hovious (8/14/2009)
Lucky, could you show us some sample data and then the expected output? I'm not sure I understand your question from what you put in the initial post.gah (8/14/2009)
Hi..maybe I have misunderstood your question....but you ask that "want products ordered by category-wise "from the table designs that you have provided I cannot see where " category" is defined?
Could you please clarify...thanks
Thanks for the replies and apologize for not giving more information.
Table Schemas
=============
Table : Dept (Category)
Column_name --> Type
dept_id --> int
parent_id --> int
dept_order --> int
name --> nvarchar(128)
Table : Dept_Product
Column_name --> Type
dept_id --> int
product_id --> int
product_order --> int
INSERT INTO Dept VALUES(1,0,1,'Shirts')
INSERT INTO Dept VALUES(2,1,2,'T-Shirts')
INSERT INT Dept VALUES(3,1,3,'Sport')
INSERT INT Dept VALUES(4,1,4,'Casual')
INSERT INT Dept VALUES((5,0,5,'Pants')
INSERT INT Dept VALUES((6,5,6,'Plain')
INSERT INT Dept VALUES(7,5,7,'Wide Leg')
INSERT INT Dept_Product VALUES(2,101,1)
INSERT INT Dept_Product VALUES(2,102,2)
INSERT INT Dept_Product VALUES(2,103,4)
INSERT INT Dept_Product VALUES(2,104,5)
INSERT INT Dept_Product VALUES(2,106,17)
INSERT INT Dept_Product VALUES(2,108,19)
INSERT INT Dept_Product VALUES(2,109,3)
INSERT INT Dept_Product VALUES(2,110,6)
INSERT INT Dept_Product VALUES(2,111,10)
INSERT INT Dept_Product VALUES(2,112,8)
INSERT INT Dept_Product VALUES(3,113,7)
INSERT INT Dept_Product VALUES(3,114,9)
INSERT INT Dept_Product VALUES(3,115,11)
INSERT INT Dept_Product VALUES(3,116,31)
INSERT INT Dept_Product VALUES(3,117,27)
INSERT INT Dept_Product VALUES(3,118,30)
INSERT INT Dept_Product VALUES(2,119,21)
INSERT INT Dept_Product VALUES(4,120,9)
INSERT INT Dept_Product VALUES(4,121,11)
INSERT INT Dept_Product VALUES(4,122,3)
INSERT INT Dept_Product VALUES(4,123,1)
INSERT INT Dept_Product VALUES(4,124,15)
INSERT INT Dept_Product VALUES(4,125,5)
INSERT INT Dept_Product VALUES(4,126,2)
INSERT INT Dept_Product VALUES(6,127,1)
INSERT INT Dept_Product VALUES(6,128,22)
INSERT INT Dept_Product VALUES(6,130,24)
INSERT INT Dept_Product VALUES(6,135,9)
INSERT INT Dept_Product VALUES(6,138,12)
INSERT INT Dept_Product VALUES(6,139,10)
INSERT INT Dept_Product VALUES(6,154,6)
INSERT INT Dept_Product VALUES(7,153,4)
INSERT INT Dept_Product VALUES(7,129,21)
INSERT INT Dept_Product VALUES(7,131,18)
INSERT INT Dept_Product VALUES(7,160,12)
INSERT INT Dept_Product VALUES(7,162,51)
INSERT INT Dept_Product VALUES(7,163,38)
INSERT INT Dept_Product VALUES(7,165,1)
The output should look like
First Column is the Parent Category (Dept)
Second Column is the product id
Third column is the ranking of the product in the sub-category and is not a requirement in the output.
As T-Shirts, Sport and Casual fall under the parent Shirts the order of products at the parent level should be as follows:
1 --> 101 --> 1
1 --> 102 --> 2
1 --> 109 --> 3
1 --> 103 --> 4
1 --> 104 --> 5
1 --> 110 --> 6
1 --> 112 --> 8
1 --> 111 --> 10
1 --> 106 --> 17
1 --> 108 --> 19
1 --> 113 --> 7
1 --> 114 --> 9
1 --> 115 --> 11
1 --> 119 --> 21
1 --> 117 --> 27
1 --> 118 --> 30
1 --> 116 --> 31
1 --> 123 --> 1
1 --> 126 --> 2
1 --> 122 --> 3
1 --> 125 --> 5
1 --> 120 --> 9
1 --> 121 --> 11
1 --> 124 --> 15
As Plain and Wide Leg fall under the parent Pants the order of products at the parent level should be as follows:
5 --> 127 --> 1
5 --> 154 --> 6
5 --> 135 --> 9
5 --> 139 --> 10
5 --> 138 --> 12
5 --> 128 --> 22
5 --> 130 --> 24
5 --> 165 --> 1
5 --> 153 --> 4
5 --> 160 --> 12
5 --> 131 --> 18
5 --> 129 --> 21
5 --> 163 --> 38
5 --> 162 --> 51
I hope this time I'm clear of my requirements.
Thanks
Lucky