In this new query the WHERE clause is correct but the JOIN is still incorrect, orders.orderer is a fk to users.user_id and not a person_id so that JOIN is wrong.
My suggestion is your SELECT after INSERT INTO should be a select from users table since the users table holds the person_id so you can get it from there which would make the JOIN be on orders.orderer and users.person_id @djazayeri i would prefer to use users.username or users.system_id as the new ly created provider's unqiue identifier, what do you think?