USERS
ID | playername [...]
-----|-------------------
1 | example1
2 | example2
KILLS
ID | killerid | victimid | weapon
-----|----------------------------------
1 | 1 | 2 | FIST
2 | 2 | 1 | PISTOL
I want to insert into KILLERS the IDs of the killer (killerid) and the victim (victimid) but I only have the 2 names (example1 and example2) when I'm inserting.
So I have to get the IDs of those 2 names in my table USERS.
Like (pseudo example):
INSERT INTO `KILLERS`(`killerid`, `victimid`, `weapon`)
VALUES(
USERS.ID WHERE USERS.playername = 'example1',
USERS.ID WHERE USERS.playername = 'example2',
'FIST'
);
That should insert:
ID | killerid | victimid | weapon
-----|----------------------------------
1 | 1 | 2 | FIST
This doesn't work:
INSERT INTO table1 (killerid)
SELECT t1.id
FROM table1 t1
LEFT JOIN table2 t2 ON (t2.killerid = t1.id)
WHERE t2.playername = 'example'
INSERT INTO kills (killerid, victimid, weapon)
SELECT u1.ID as killerid,
u2.ID as victimid,
d.weapon
FROM USERS u1
INNER JOIN USERS u2
ON 1 = 1
INNER JOIN (SELECT 'FIST' as weapon
FROM DUAL) d
ON 1 = 1
WHERE u1.playername = 'example1' AND
u2.playername = 'example2';