MYSQL Triggers with an inner join

Question

I have 2 tables called users and jobs. I have made a third table which is an inner join of users and jobs called jobPosts. My question is to do with when I insert values into the jobs table that it will also insert into the new jobPosts table. Here is my attached SQL but its failing to work for me.

CREATE DEFINER=`root`@`localhost` TRIGGER trg_jobs_after_insert 
 AFTER INSERT ON jobs
 FOR EACH ROW
BEGIN
INSERT INTO jobPost (userID, name, email, phoneNo, location, jobID, 
title, level, dateFrom, dateTo, description) 
SELECT 
     users.name
     , users.email
       , users.phoneNo
         , users.location
     , NEW.jobID
     , NEW.title
     , NEW.level
     , NEW.dateFrom
     , NEW.dateTo
      , NEW.description
  FROM users
  WHERE users.userID= NEW.user.userID;
 END

Tables ->

Jobs Table:

Users Table:

Answer 1

Your where condition needs to be on email instead of userID. Also, you are missing userId in select for inserting.

DELIMITER //
CREATE DEFINER=`root`@`localhost` TRIGGER trg_jobs_after_insert 
 AFTER INSERT ON jobs
 FOR EACH ROW
BEGIN
  INSERT INTO jobPost (userID, name, email, phoneNo, location, jobID, 
                       title, level, dateFrom, dateTo, description) 
  SELECT 
     users.userID
     , users.name
     , users.email
     , users.phoneNo
     , users.location
     , NEW.jobID
     , NEW.title
     , NEW.level
     , NEW.dateFrom
     , NEW.dateTo
     , NEW.description
  FROM users
  WHERE users.email = NEW.email;
 END //
 DELIMITER ;