Aaron, It sounds like the join has duplicates on both sides. If you join a->b where a is unique (like a primary key) then you will get count(b) records (where b is the rows that match a). Or if b is unique then you will get count(a) records. However if neither a or b is unique you get count(a) * count(b) records. Without you tabledefs it is difficult to see if this is the case.
Try this: select count(*) as xx from kcs_threadgroups group by threadType having xx > 1; If you get a result then you have duplicates on threadType select count(*) as xx from kcs_threads where manufacturer='DMC' group by type having xx > 1; If you get a result then you have duplicates on Type for manufacturer DMC. If you have dups for both then you are getting the result you are asking for. If this doesn't help, please publish your tabledefs. It's ok to simplify them so we don't have to wade through tons of stuff that has nothing to do with the problem. Regards, Andy. Aaron Wolski wrote: > > Hi Guys, > > I have 2 queries: > > select * FROM kcs_threadgroups AS t1, kcs_threads AS t2 WHERE > t2.manufacturer='DMC' AND t1.thread_index=t2.id ORDER BY t2.type, > t1.groupName > > Using the explain command (explain select.) I get: > > +-------+--------+---------------+---------+---------+-----------------+ > ------+---------------------------------+ > | table | type | possible_keys | key | key_len | ref | > rows | Extra | > +-------+--------+---------------+---------+---------+-----------------+ > ------+---------------------------------+ > | t1 | ALL | NULL | NULL | NULL | NULL | > 286 | Using temporary; Using filesort | > | t2 | eq_ref | PRIMARY | PRIMARY | 4 | t1.thread_index | > 1 | where used | > +-------+--------+---------------+---------+---------+-----------------+ > ------+---------------------------------+ > > > > select * FROM kcs_threadgroups AS t1, kcs_threads AS t2 WHERE > t2.manufacturer='DMC' AND t1.threadType=t2.type ORDER BY t2.type, > t1.groupName > > Using the explain command I get: > > +-------+------+---------------+------+---------+------+------+--------- > ------------------------+ > | table | type | possible_keys | key | key_len | ref | rows | Extra > | > +-------+------+---------------+------+---------+------+------+--------- > ------------------------+ > | t1 | ALL | NULL | NULL | NULL | NULL | 286 | Using > temporary; Using filesort | > | t2 | ALL | NULL | NULL | NULL | NULL | 2067 | where > used | > +-------+------+---------------+------+---------+------+------+--------- > ------------------------+ > > > With the second query, I am getting over 77,000 results returned and > with the first query I am getting 2067 - the correct amount. > > The only differences between the 2 is that in the first I have WHERE > clause t1.thread_index=t2.id and in the second I have > t1.threadType=t2.type > > I need to have the WHERE like the second query but for the life of my I > have no clue why it is returning over 77,000 results. > > Can anyone help guide me to get the results of #1 but with the WHERE of > #2? > > Thanks so much! > > Aaron > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
