Stumped..............................

[xboredx]

I have three tables. Sales, Leads, and one that ties them together.

sales tbl has (id, name) for fields
leads tbl has (id, name) for fields
tie tbl has (id, subID)

Lets say the sales table contains the following data
1, Jim

Lets say the leads table contains
2, Matt
3, Jake
5, Renee
7, Jenny
8, George
9, Frank
10, Curtis

The tie table then contains
1,2
1,3
1,5
3,7
7,8
5,9
5,10

Showing:
Jim
-Matt(direct)
-Jake (direct)
–Jenny (indirect level 1)
—George (indirect level 2)
-Renee(direct)
–Frank (indirect level 1)
–Curtis (indirect level 1)

Any ideas on how I would query based on a sales person, the direct and indirect leads under them? My goal would be to go unlimited levels. I nested while loops, and came up with a patched version of this, but it is too much hassle to work with. Anyone ever done anything like this before?

[peg110]

HUH???

I think your TIE table needs some work.

You need something to link the tables so that you know whether the “id” links with SALES or LEADS. Since you have nothing like that, you would likely have meaningless data.

If TIE ties SALES and LEADS together then might I suggest that you add (at least ) a third column. Thus you would have id, salesId, leadsId.

That will allow you to link the tables.

As for your output (and how to query to get the results) I am confused at how you intend to get that output (again based on the table structure).

[Zyppora]

If every entry in leads only has one ID to be linked with from sales, you might as well drop the tie table, and use the following structure for leads:

ID - Name - SalesID