use last_insert_id() /not

[shaddf]

ihave this table structure

CREATE TABLE staff_details_tbl (
staff_id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
SUsername_id   varchar(255),
Fname VARCHAR(60) NOT NULL,
Lname VARCHAR(60) NOT NULL,
D_O_B date,
PRIMARY KEY (staff_id )
);

Fname and Lname for more than 1 human being can be the same
i have this stored procedure code:

declare l_staff_id int;
  insert into staff_details_tbl (Fname,Lname,D_O_BSUsername_id)values(inFname,inLname,inBdate,' ');
select staff_id  into l_staff_id from staff_details_tbl where Fname=inFname and Lname=inLname ;
insert into staff_job_tbl(staff_id,Team_catId,job_id)values(l_staff_id,inteam,injob);

ihave to select the staffid for insert into another table,
there are some problems with using the last_insert_id()  that is why i choose to select the staff_id here.


but with no unique Fname or Lname,the repetition of same names  causes an error of REsult contained more than one row

 no unique key because there re many people with a name like 'kamikazi' google and see.
I need a way of getting that id how can I go about this??

[benanamen]

ihave to select the staffid for insert into another table

 

Why? What is the REAL problem you are trying to solve. See the XY Problem in my signature.

[NigelRel3]

there are some problems with using the last_insert_id()  that is why i choose to select the staff_id here.

What do you think are the problems which stops you using this?