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.

score 3 · Accepted Answer · answered Jul 03 '16 at 12:23

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.

score 2 · Answer 2 · answered Jul 03 '16 at 09:55

You need to join these tables together :

INSERT INTO posts (post_category, post_author, post_title, post_content) 
SELECT c.category_name,a.author_name,'Lorem Ipsum','Lorem Ipsum Lorem Ipsum  Lorem Ipsum  Lorem Ipsum'
FROM categories c
CROSS JOIN authors a
WHERE c.category_name = 'python'
  AND a.author_name = 'm0jito'

I used CROSS JOIN because you didn't provide any relations between those two tables(a little suspicious) , if there is some sort of relation column, change it to an INNER JOIN and use the ON() clause.

Insert 2 values from two different tables into one table

2 Answers2