Create table from two another tables

[ScorpAL]

Hi,
I have some problem...

I have two tables phpbb_posts and phpbb_posts_new with same fields and structure:

CREATE TABLE `phpbb_posts` (
  `post_id` mediumint(8) unsigned NOT NULL auto_increment,
  `topic_id` mediumint(8) unsigned NOT NULL default '0',
  `forum_id` smallint(5) unsigned NOT NULL default '0',
  `poster_id` mediumint(8) NOT NULL default '0',
  `post_time` int(11) NOT NULL default '0',
  `post_created` int(11) NOT NULL default '0',
  `poster_ip` varchar(8) NOT NULL default '',
  `post_username` varchar(25) default NULL,
  `enable_bbcode` tinyint(1) NOT NULL default '1',
  `enable_html` tinyint(1) NOT NULL default '0',
  `enable_smilies` tinyint(1) NOT NULL default '1',
  `enable_sig` tinyint(1) NOT NULL default '1',
  `post_edit_time` int(11) default NULL,
  `post_edit_count` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`post_id`),
  KEY `forum_id` (`forum_id`),
  KEY `topic_id` (`topic_id`),
  KEY `poster_id` (`poster_id`),
  KEY `post_time` (`post_time`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=46104 ;

At phpbb_posts I have 58312 records.
At phpbb_posts_new I have 43470 records.

The phpbb_posts_new table is part of backup of phpbb_posts making at November 2006.
Why only part instead of full backup? Because archieve was broken :(

Two days ago by accident I delete more then 35000 records from my forum.
Currently I try repair part of them from backup.

Ok.
We have two tables.
At phpbb_posts I have 58312 records after accident.
And phpbb_posts_new has 43470 records.

What I need now...
I need create third table for example phpbb_posts_rep aired where place records from phpbb_posts_new which not exist in phpbb_posts.

Each record has unique `post_id`.

I am not MySQL guru.
I realy need help.

Thanx for all who will try help me.

[pbmods]

Heya, ScorpAL. Welcome to TSDN!

Try using a MySQL NOT EXISTS subquery:

INSERT
    INTO
        `phpbb_posts_repaired`
    SELECT
            *
        FROM
            `phpbb_posts_new`
        WHERE
            NOT EXISTS
            (
                SELECT
                        *
                    FROM
                        `phpbb_posts`
                    WHERE
                        `phpbb_posts`.`post_id` = `phpbb_posts_new`.`post_id`
            )
 

[ScorpAL]

WOW!
pbmods, you help me recover 13,801 posts!
Thanx a lot!

Later I will try recover phpbb_posts_tex t table.
And then generate phpbb_topics table.

Thanx again.

[pbmods]

Heya, ScorpAL.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)