4 messages in com.mysql.lists.mysqlRe: strange difference in creating vi...
FromSent OnAttachments
Hector Villafuerte27 Oct 2004 19:20 
SGr...@unimin.com28 Oct 2004 07:25 
Elim Qiu28 Oct 2004 11:12 
SGr...@unimin.com28 Oct 2004 13:57 
Subject:Re: strange difference in creating views (mysql 5.0.1)
From:SGr...@unimin.com (SGr@unimin.com)
Date:10/28/2004 01:57:52 PM
List:com.mysql.lists.mysql

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" <el@elinkage.net> 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?