mysql insert information from multiple rows from another table

Question

I am using MySQL version 5.6. I have a table event that takes two ids and a date, and a table student that contains a column id. I want to insert two ids when the name match (WHERE firstname =.., AND lastname=..,). However, I don't quite know how to take id from two rows in one insert command. Can any one help me please?

Thanks, Paul

Answer 1

If you are trying to insert into a table, then you want to use insert. To get data from another table or query, you would want the insert . . . select form.

From what you say, this seems something like what you want:

insert into event(id1, id2, date)
    select s1.id, s2.id, now()
    from student s1 cross join
         student s2
    where s1.id <> s2.id and
          s1.firstname = firstname and s1.lastname = lastname and
          s2.firstname = firstname and s2.lastname = lastname;

It would return all pairs of students where the names match (but don't have the same id).