'Operand should contain 1 column(s)' error when inserting two tables into one in MySQL

Question

I want to insert two different tables into one as per the below illustration:

I've tried several ways like:

INSERT INTO com_short 
    (category_id, media_folder_id, gallery_folder_id, status, important, 
    bgPositioning, published_at, published_by, created_at, modified_at,title, 
    slug, body, featured_image_file_id) 

SELECT (SELECT com_post.id, category_id, media_folder_id, gallery_folder_id,    
               status, important, bgPositioning, published_at, published_by, 
               com_post.created_at, com_post.modified_at 
        FROM com_post 
            INNER JOIN com_post_translation ON com_post.id = com_post_translation.post_id 
        WHERE com_post_translation.post_id = com_post.id
        ),

        (SELECT title, slug, body,featured_image_file_id 
        FROM com_post_translation 
        WHERE com_post.id = com_post_translation.post_id
        );

But it shows an error as follows:

What am I missing?

Answer 1

Reason of the error: you can not select more than one column in the subquery:

SELECT (SELECT com_post.id, category_id, media_folder_id, gallery_folder_id,    
               status, important, bgPositioning, published_at, published_by, 
               com_post.created_at, com_post.modified_at 
        FROM com_post 
            INNER JOIN com_post_translation ON com_post.id = com_post_translation.post_id 
        WHERE com_post_translation.post_id = com_post.id
        ),

        (SELECT title, slug, body,featured_image_file_id 
        FROM com_post_translation 
        WHERE com_post.id = com_post_translation.post_id
        );

Simple explanation :

This will produce error:

select (select '1', '2');

This will not:

select (select '1');

Check the demo

What would be the way to do it withot errors:

INSERT INTO com_short (category_id --1
                       , media_folder_id --2
                       , gallery_folder_id --3
                       , status --4
                       , important --5
                       , bgPositioning --6
                       , published_at --7
                       , published_by --8
                       , created_at --9
                       , modified_at --10
                       , title --11
                       , slug --12
                       , body --13
                       , featured_image_file_id) --14 
SELECT cpt.category_id --1
       , cpt.media_folder_id --2
       , cpt.gallery_folder_id --3
       , cpt.status --4
       , cpt.important --5
       , cpt.bgPositioning --6
       , cpt.published_at --7
       , cpt.published_by --8
       , com_post.created_at --9
       , com_post.modified_at --10
       , cpt.title --11
       , cpt.slug --12
       , cpt.body --13
       , cpt.featured_image_file_id --14
FROM com_post 
INNER JOIN com_post_translation cpt ON com_post.id = cpt .post_id

Note: This is just an example, I can not know for sure from your question what do you want to do....

Additional info:

• cpt is alias for the table com_post_translation (It makes your code "lighter" and easyer to read).

• When you join two tables with ON keyword there is no need to repeat that condition in the where clause.