n3stis n3stis - 1 year ago 51
SQL Question

Insert from two different tables

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

First table is:

author_id (PK)



Second table is:

category_id (PK)


Third table is:







and I want get from first table author_name and from second table category_name during inserting data to 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 Source

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.