#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Location
    Mexico, DF
    Posts
    249
    Rep Power
    12

    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
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Location
    Mexico, DF
    Posts
    249
    Rep Power
    12
    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)
    Regards. Germán.

    "Lo importante no es saber, sino tener el teléfono del que sabe." - Les Luthiers
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Mar 2003
    Posts
    704
    Rep Power
    61
    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
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Location
    Mexico, DF
    Posts
    249
    Rep Power
    12
    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
    Regards. Germán.

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

IMN logo majestic logo threadwatch logo seochat tools logo