I am dealing with two tables. One, I'll call table1 has about 90 columns and 20k rows, each number in column "xy" is unique. The other has about 90 columns and about 200k rows, and there will be around 10 duplicate entries in "xy" for each value of "xy". So, if I SELECT a given number in table1.xy, one row is returned, then if I SELECT the same number in table2.xy, about 10 rows will be returned. There is always at least one entry in table2.xy for each entry in table1.xy
I am looking for the best way to join them so for each row in the result is a row of table1 lined up with one (and only one) row from table2.
I'd better ask the obvious question... *which* one? Is there one in particular that you're after, or would you be equally happy with any of them? The latter would be a bit odd.
I am aware that I could simply GROUP BY xy, but I am also trying to use this with a few AVG(<some other column>) clauses to get the average of all rows returned. GROUP BY xy only returns the average of each grouping. I am also guessing that GROUP By in this instance would be rather inefficient because It would first have to build a large table (>200k rows) and then SELECT from it.
This is complex enough so that I'll need a more detailed example of your query to explain what you're trying to accomplish. But, on the surface, it sounds as though you're already using GROUP BY in the same SELECT, in
which case you won't be taking on a significant amount of additional
overhead -- you're already scanning all the table2 rows because of your
GROUP BY. Or, are you talking about multiple SELECT statements?
Bruce Feist
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
