I have 3 tables
I want when inserting into works table records in one query to insert into columns: name, date (by hand), then also into type_id and employer_id columns (selecting id from other tables)
I know you can insert and then use select, but how do i add the other values, that i need to type by hand as they dont exist on other tables?
You can just put the extra values directly in the
INSERT INTO works (name, date_started, type_id, employer_id) SELECT name, curdate(), type_id, employer_id FROM types t JOIN employers e ON ... WHERE ...
You can have subqueries inside
VALUES() clause, provided the subquery will only return one value.
INSERT INTO works (name, date_started, date_finished, type_id, employer_id) VALUES ( 'name here', 'date started', 'date finished', (SELECT id FROM types WHERE type = 'val' LIMIT 1), (SELECT id FROM employers WHERE name = 'val' LIMIT 1) )