Select from two tables not returning the expected number of rows

[Ted Byrne]

I'm struggling with this, and am not sure what I'm doing wrong...  There 
are two tables in separate databases that have 6 fields in common. In 
addition to the six columns in common between table_a and table_b, each 
table has some additional fields that are not related.  Table_a and table_b 
each have exactly the same number of records (19,205).

Table_a has correct values in all of these common fields, except for one 
column, action.  I'd like to set table_a.action to the value of 
table_b.action, when the remaining 5 fields in common match.

Before I tried updating table_a in database x, I wanted to make sure that I 
was specifying the conditions in the where clause correctly so that I would 
be setting table_a.action to the correct value.

However, the select that I came up with is returning more than 3x the 
number of records that I expected.  Table_a and table_b each have just over 
19K records, but the result set of my query is over 61K records.  I was 
expecting that I would get exactly the same number of records as are in 
table_a and table_b

What am I doing wrong here?  My first thought was that I might need to use 
a JOIN, but that is new territory for me, and I'm not somehow not getting 
the syntax right.  (Any recommendations for reading on the different types 
of joins and when to use particular kinds would be greatly appreciated...)

The query and results are included below, as well as a count from each of 
the individual tables.  Just in case it's relevant, the server version is 
4.0.13, running on Windows 2K.

Thanks,
Ted
mysql>select count(*)
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;
+----------+
| count(*) |
+----------+
|    61670 |
+----------+
1 row in set (5.54 sec)
mysql> select count(*) from y.table_b;
+---------------+
| count(*) |
+---------------+
|         19205 |
+---------------+
1 row in set (0.14 sec)
mysql> select count(*) from x.table_a;
+---------------+
| count(*) |
+---------------+
|         19205 |
+---------------+
1 row in set (0.15 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]