Extremely complex Select statement

Hello,
I'm having real problems defining a SELECT statement for
the following scenario:
I have a Requirements table, each row in this table has
at least 1 row in the RequirementsWeeks table which
defines values for N weeks for each requirement.
Also, each requirement has a product value and a tool
value and each product belongs to a prodCategory and each
tool belongs to a ToolCategory (these associations are
stored in separate tables)
Here's an example of the records I have:
Requirements: RequirementsWeeks:
Product Tool Id ReqId Week Value
A TA 1 1 8 0.20
C TB 2 1 9 0.35
2 8 2.56
2 9 3.52
Products: Tools:
Id ProductName ProCategoryID Id ToolName ToolCatID
1 A 45 1 TA 68
2 B 53 2 TB 68
3 C 45
What i want to have is:
For all pairs product-tool in requirements that matches
ToolCatID And ProdCatID return the sum for week N
For example:
ToolCatID:68 And ProdCatID:45 the sum will return 0.20 +
2.56
And I need this to be done for All posible combinations
of ToolCatID and ProdCatID.
Any thoughts? I don't want to loop through a single SQL
statement that receives toolCat and prodCat as parameters,
I thought there's a way for SQL to manage this.
Thank you a lot for your time! I'm really desperate!Yes, exactly. Thanks a lot for your time, I've already
found a solution, which is to divide the problem in two
parts: The statement to select each row, and the one to
select that for all possible combinations of toolcat and
product cat(which was a very easy select). This seems to
work well but if you have a better idea please let me know.
Thank you so much for your time!! I really appreciate it!
Marcela
>--Original Message--
>So based on your example is this what you want the output
to be
>Toolcatid ProDCatID Sum Week
>68 45 0.2 + 2.56 8
>68 45 0.35+3.52 9
>68 53 0.2 + 2.56 8
>68 53 0.35+3.52 9
>
>"Marcela" <marcela.villalobos@.yahoo.com> wrote in message
>news:057b01c35bb1$af9e9310$a401280a@.phx.gbl...
>> Hello,
>> I'm having real problems defining a SELECT statement
for
>> the following scenario:
>> I have a Requirements table, each row in this table has
>> at least 1 row in the RequirementsWeeks table which
>> defines values for N weeks for each requirement.
>> Also, each requirement has a product value and a tool
>> value and each product belongs to a prodCategory and
each
>> tool belongs to a ToolCategory (these associations are
>> stored in separate tables)
>> Here's an example of the records I have:
>> Requirements: RequirementsWeeks:
>> Product Tool Id ReqId Week Value
>> A TA 1 1 8 0.20
>> C TB 2 1 9 0.35
>> 2 8 2.56
>> 2 9 3.52
>> Products: Tools:
>> Id ProductName ProCategoryID Id ToolName
ToolCatID
>> 1 A 45 1 TA 68
>> 2 B 53 2 TB 68
>> 3 C 45
>>
>> What i want to have is:
>> For all pairs product-tool in requirements that matches
>> ToolCatID And ProdCatID return the sum for week N
>> For example:
>> ToolCatID:68 And ProdCatID:45 the sum will return
0.20 +
>> 2.56
>> And I need this to be done for All posible combinations
>> of ToolCatID and ProdCatID.
>> Any thoughts? I don't want to loop through a single SQL
>> statement that receives toolCat and prodCat as
parameters,
>> I thought there's a way for SQL to manage this.
>> Thank you a lot for your time! I'm really desperate!
>>
>
>.
>