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" <elim@stripped> 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?
>
>
