By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,292 Members | 1,381 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,292 IT Pros & Developers. It's quick & easy.

Joining multiple tables with one to many relationships

100+
P: 111
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!!
Aug 12 '08 #1
Share this Question
Share on Google+
2 Replies


10K+
P: 13,264
Can you describe what you want the query to return and provide some sample output if possible.
Aug 13 '08 #2

100+
P: 310
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.
Aug 13 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.