I suspect that you actually have two records in address_association that don't match up with a record in address. Yes, either a data mismatch or data duplication would be suspected in a case like this.
Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Elim Qiu" <[EMAIL PROTECTED]> wrote on 10/28/2004 02:12:54 PM: > I'm trying to create a view from two tables address and address_association. > So I did the following and expect they are the same: > > CREATE VIEW associated_address AS > select t0.association_id AS association_id, > t0.property_dict AS property_dict, > t0.type_id AS type_id, > t0.owner_id AS owner_id, > t0.owner_class_name AS owner_class_name, > t0.status_code AS asso_status, > t0.flag AS flag, > t1.* from address_association t0, address t1 > where (t0.address_id = t1.address_id); > > CREATE VIEW vaa AS > select t0.association_id AS association_id, > t0.property_dict AS property_dict, > t0.type_id AS type_id, > t0.owner_id AS owner_id, > t0.owner_class_name AS owner_class_name, > t0.status_code AS asso_status, > t0.flag AS flag, > t1.* from address_association t0 > left join address t1 > on (t0.address_id = t1.address_id); > > But you see the differences: > > mysql> select count(1) from vaa; > +----------+ > | count(1) | > +----------+ > | 1443 | > +----------+ > 1 row in set (7.30 sec) > > mysql> select count(1) from associated_address; > +----------+ > | count(1) | > +----------+ > | 1441 | > +----------+ > 1 row in set (3.32 sec) > > I have one row in address_association which address_id value not > found in table address. > Does this cause the above difference? > >
