Insert 2 values from two different tables into one table

Question

I have a problem with insert 2 values from 2 different tables during inserting to third table.

First table is:

• author_id (PK)
• author_name
• author_email

Second table is:

• category_id (PK)
• category_name

Third table is:

• post_id
• post_category
• post_author
• post_title
• post_content
• post_date

and I want get author_name from the first table and category_name from the second table during inserting data into third table.

I got something like this but it's not working.

INSERT INTO posts (post_category, post_author, post_title, post_content) 
    SELECT 
        category_name 
    FROM 
        categories 
    WHERE 
        category_name='python', 
    SELECT 
        author_name 
    FROM
        authors 
    WHERE
       author_name = 'm0jito', 
    'Lorem Ipsum', 
    'Lorem Ipsum Lorem Ipsum  Lorem Ipsum  Lorem Ipsum ')

Looking forward for your help.

Answer 1

Besides @sagi's solution you should be able to use Scalar Subqueries in an INSERT VALUES:

INSERT INTO posts (post_category, post_author, post_title, post_content) 
VALUES
  (
    (SELECT 
        category_name 
    FROM 
        categories 
    WHERE 
        category_name='python'), 
    (SELECT 
        author_name 
    FROM
        authors 
    WHERE
       author_name = 'm0jito'), 
    'Lorem Ipsum', 
    'Lorem Ipsum Lorem Ipsum  Lorem Ipsum  Lorem Ipsum '
  )

This will fail if category_nameor author_name are not unique.

Btw, you probably want to return the category/author id instead of the name, because the current SELECT is not needed at all. But I assume this was just an example.