#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    6
    Rep Power
    0

    Creating a new table with info from two different tables?


    I know I am in over my head, but I am learning. Thanks for your patience in advance.

    I have a table with this structure:

    maxmag_kunena_messages


    AND one with this structure:

    maxmag_kunena_messages_text


    That I want to combine to a table with THIS structure (id is a common column):

    posts
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    6
    Rep Power
    0
    I managed to get all of the info from maxmag_kunena_messages into posts using:

    Code:
    INSERT INTO posts (`id`, `parent`, `thread`, `catid`, `name`, `userid`, `email`, `subject`, `time`, `ip`, `topic_emoticon`, `locked`, `hold`, `ordering`, `hits`, `moved`, `modified_by`, `modified_time`, `modified_reason`) 
    SELECT `id`, `parent`, `thread`, `catid`, `name`, `userid`, `email`, `subject`, `time`, `ip`, `topic_emoticon`, `locked`, `hold`, `ordering`, `hits`, `moved`, `modified_by`, `modified_time`, `modified_reason`     
    	FROM `maxmag_kunena_messages`
    However I can't seem to figure out how to add
    message in maxmag_kunena_messages_text

    to

    message in posts

    matching them based on id...

    tried:

    Code:
    INSERT INTO posts (`message`) 
    SELECT `message`     
    	FROM `maxmag_kunena_messages_text`
            WHERE `maxmag_kunena_messages_text`.`id` = `maxmag_kunena_messages`.`id`
    and

    Code:
    INSERT INTO posts (`message`) 
    SELECT `message`     
    	FROM `maxmag_kunena_messages_text`
            WHERE `maxmag_kunena_messages_text`.`id` = `posts`.`id`
    but got error:

    #1054 - Unknown column 'maxmag_kunena_messages.id' in 'where clause'

    I am assuming I am missing something obvious, any one care to point out what exactly that is?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Italy
    Posts
    36
    Rep Power
    2
    What you need is a simple JOIN. Something like:

    Code:
    CREATE TABLE `posts`
    SELECT `mk`.`id` AS `id`, `mk`.`parent` AS `parent`, `mk`.`thread` AS `thread`, ... , `mkm`.`message` AS `message`
    	FROM `maxmag_kunena` `mk` INNER JOIN `maxmag_kunena_messages` `mkm`
    	ON `mk`.`id` = `mkm`.`id`;
    (add the missing fields and check that the syntax is right, i didn't test it)

    But maybe those table are separated for a reason (different storage engines?). Why don't you use a view, instead of merging the tables?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    6
    Rep Power
    0
    f_razzoli: Thanks for your response, I will run it and let you know if it works out. I am merging the tables because I am blundering my way through an attempt at a forum import from one platform to another. Thanks again.

IMN logo majestic logo threadwatch logo seochat tools logo