List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 28 2004 8:58pm
Subject:Re: strange difference in creating views (mysql 5.0.1)
View as plain text  
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?
> 
> 

Thread
Subqueries and JOINHector Villafuerte28 Oct
  • Re: Subqueries and JOINSGreen28 Oct
    • strange difference in creating views (mysql 5.0.1)Elim Qiu28 Oct
      • Re: strange difference in creating views (mysql 5.0.1)SGreen28 Oct