[Neekos]

Im working on web tool that will allow supervisors to see a list of their agents and their call stats for a call center.

I have one main table that holds employee IDs and their supervisor names. I then have a dozen other tables that hold many different employee statistics and scores that they accumulate through the year. These tables all have employee IDs in them, but the IDs would reoccur everyday (so they are not unique) and there is data for every day of the year (i eventually need to factor in the date fields to allow them to query by dates). If i join just one table linked on IDs, i get correct results. If i add a second table linked on IDs, i suddenly get a cartesian product of those two tables. (if i query for just one employee: table one has 118 records for that employee. table two has 125 records. when i query both tables linked by ID i get 14,750 records! (118*125))

Here is part of my sql (ive basically stripped it down and have excluded many of the tables for now):

NOTE: all ID fields are employee ID numbers - which would be the common link between all of the tables.

Expand|Select|Wrap|Line Numbers

1. SELECT supes.ID, supes.firstname, supes.lastname, supes.supe,  imssi.ID, imssi.numOfSI, imssi.siDate, imsactivites.actDate, imsactivites.ID, imsactivites.numOfAct, imsCalls.ID, imsCalls.calls_hnld
2. FROM supes 
3. LEFT OUTER JOIN imssi
4. ON supes.ID=imssi.ID
5. LEFT OUTER JOIN imsactivites
6. ON supes.ID=imsactivites.ID
7. LEFT OUTER JOIN imsCalls
8. ON supes.ID=imsCalls.ID
9. WHERE supes.supe='$supe'
10. GROUP BY supes.ID
11. ORDER BY supes.lastname;

This query will take forever to run, as every join is exponentializing the data. What am i doing wrong or does anyone have any suggestions on how to fix this?

Just a note to give you more insight: These tables originally started with only one day's worth of data in them for testing purposes and at that point it worked being it was a one to one relationship. As soon as i added YTD data (making the ID no longer unique), i started having the problem. Any thoughts or ideas would be greatly appreciated!!

[r035198x]

Can you describe what you want the query to return and provide some sample output if possible.

[coolsti]

You should also post the table structures (or at least the essential columns in them). Try posting the CREATE TABLE syntax, or the output from the "SHOW CREATE TABLE xxxx" query.