Complex relationship query

[williey]

I need some help trying to figure out the query for a set of business rules.

The result should look something like this and col d should show the total dollar amount for the relationship.

col a col b col c
----- ----- -----
name 1 partyid 1 item# 1 \
name 2 partyid 2 item# 1 \
name 3 partyid 3 item# 1 \
\
name 2 partyid 2 item# 2 \
relationship item# 1,2,3,4
name 3 partyid 3 item# 2 /
name 4 partyid 4 item# 2 /
/
name 4 partyid 4 item# 3 /
/
name 4 partyid 4 item# 4 /

name 5 partyid 5 item# 5 \
name 6 partyid 6 item# 5 \
name 7 partyid 7 item# 5 \
\
name 7 partyid 7 item# 6 / relationship item# 5,6,7
/
name 7 partyid 7 item# 7 /
name 8 partyid 8 item# 7 /

name 9 partyid 9 item# 8 \
name 10 partyid 10 item# 8 \
relationship item 8,9
name 9 partyid 9 item# 9 /
name 10 partyid 10 item# 9 /


There are 3 tables involved.

Party Table A
Partyid (PK)
Item#
Name

Item_Party Table B
Item_Partyid (PK)
Item#
Partyid

Item Table C
Item# (PK)
Amount




------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[johnherman]

How do you know that Item 1-4, 5-7, and 8-9 are together? What is the rule/logic for grouping relationships?

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[williey]

the rules for grouping relationship are based on many-to-many relationship between Table A and Table B.

I tried using recursive query to pull all the related records but the results are not perfect.

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.