Merging two tables with different structure . MySQL 4.1.15

Dev Shed Forums Sponsor:

June 27th, 2007, 03:16 PM

microbati

Contributing User

Join Date: Jul 2004

Location: Mexico, DF

Posts: 249

Time spent in forums: 3 Days 18 h 9 m 8 sec
Reputation Power: 10

Merging two tables with different structure . MySQL 4.1.15

Hi!,

I'm trying to figure out what might be the best solution for this puzzle:

Having two different tables which have some fields in common but others aren't end up with a merged table containing information from both of them.

Better explained in the example:

Code:

TABLE A:
case_id VARCHAR (8)
customer_name VARCHAR (30)
owner VARCHAR (15)
dispatch_time FLOAT
type VARCHAR(4)

TABLE B:
case_id VARCHAR(8)
subcase_id VARCHAR(14)
customer_name VARCHAR (30)
owner VARCHAR (15)
response_time FLOAT
type VARCHAR(4)

Example Data:

TABLE A data:
case_id		customer_name		owner		dispatch_time	type
A2308		Peter Smith		agent_1		3.567		CASE
A2309		Peter Smith		agent_2		0.244		CASE
A2315		Dan Hurd		agent_1		1.354		CASE

TABLE B data:
case_id		subcase_id		customer_name		owner		response_time	type
A2308		A2308-1			Peter Smith		agent_5		4.232		SUBK
A2308		A2308-2			Peter Smith		agent_9		0.34345		SUBK


MERGED WANTED DATA:
case_id		subcase_id		customer_name		owner		dispatch_time	response_time	type
A2308		NULL			Peter Smith		agent_1		3.567		NULL		CASE
A2309		NULL			Peter Smith		agent_2		0.244		NULL		CASE
A2315		NULL			Dan Hurd		agent_1		1.354		NULL		CASE
A2308		A2308-1			Peter Smith		agent_5		NULL		4.232		SUBK
A2308		A2308-2			Peter Smith		agent_9		NULL		0.34345		SUBK

There is any way to do that?

__________________
Regards. Germán.

"Lo importante no es saber, sino tener el teléfono del que sabe." - Les Luthiers

June 27th, 2007, 05:29 PM

microbati

Contributing User

Join Date: Jul 2004

Location: Mexico, DF

Posts: 249

Time spent in forums: 3 Days 18 h 9 m 8 sec
Reputation Power: 10

Hi,

I think that I solved it, just needed to force matching the number of columns.

Code:

(SELECT case_id, NULL AS 'subcase_id', customer_name, owner, dispatch_time, NULL AS 'response_time', `type` FROM A)
UNION
(SELECT case_id, subcase_id, customer_name, owner, NULL as 'dispatch_time', response_time, `type` FROM B)

June 28th, 2007, 09:24 AM

blockcipher

Contributing User

Join Date: Mar 2003

Posts: 704

Time spent in forums: 3 Days 8 h 36 m 26 sec
Reputation Power: 60

Glad you figured it out. Just wanted to suggest that if you need to keep the underlying data structure, you could create a view with that query. It won't work in MySQL < 5.0, but you may want to keep it in mind if you ever upgrade or if you want an excuse to upgrade.

__________________
blockcipher
---------------
Gratuitously stolen...
mysql> SELECT * FROM user WHERE clue > 0;
0 Results Returned.

PHP5/MySQL/UTF-8
My Tech Blog

June 28th, 2007, 10:03 AM

microbati

Contributing User

Join Date: Jul 2004

Location: Mexico, DF

Posts: 249

Time spent in forums: 3 Days 18 h 9 m 8 sec
Reputation Power: 10

Hi blockcipher,

Thanks for your answer, and yes, upgrading is under scope and I strongly agree with your suggestion about creating a view.

Just need to find a time frame to do all the checking needed to ensure compatibility and functionability

Merging two tables with different structure . MySQL 4.1.15

Developer Shed Advertisers and Affiliates