n3stis n3stis - 5 months ago 9
SQL Question

Insert 2 values from two different tables into one table

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

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.