Ted, You didn't even say that (svr, started, ended, volume, who, action) was a unique combination of values for either table. If that is the case then you will have a hard time matching rows of one table uniquely to rows of the other table.
Try this query and you may be able to see where the duplicate rows are coming from: select x.table_a.*, y.table_b.* from x.table_a,y.table_b WHERE x.table_a.svr = y.table_b.svr and x.table_a.started = y.table_b.started and x.table_a.ended = y.table_b.ended and x.table_a.volume = y.table_b.volume and x.table_a.who = y.table_b.who; LIMIT 100; Concentrate on those rows that are duplicated from table_a and compare values left-to-right between the two tables, you should be able to see where there are duplicate rows on the "a" side that match rows on the "b" or duplicate rows on the "b" side that match a rows on the "a" side. Creating a unique index on the combination (svr, started, ended, volume, who) will help you in the future, but not right now, as you already have duplicates in your data. You will have to winnow them out of your data before you can create the index. Once that combination of fields is unique for both tables, your query should work as you wanted. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ted Byrne <[EMAIL PROTECTED]> wrote on 10/13/2004 08:59:04 AM: > > >you didn't say that (svr,started,ended,volume,who) is a unique key... > >If not, it would explain the result and the extra rows you get. > > > Well... that combination of fields *should* be unique. Does a unique index > need to be specified on those columns? It is possible that > (svr,started,ended,volume,who) may not be unique, and that duplicate data > was inserted into the table. > > I was assuming that my beginning SQL skills were to blame for the > unexpected results. If I understand your comment correctly, if the > combination of columns shown above is indeed a unique key, then the > results of my query should have been what I expected. Please correct me if > that is not the case. > > Thanks for the feedback, > > Ted > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
