n3stis n3stis - 5 months ago 8
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)

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 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

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.