How to create a complex insert query?

[Linda van der Pal]

Hello all,

I tried the following query:

insert into user_has_role (User_ID, Friend_ID, Role_ID)
(SELECT UID, FID, RID FROM
(SELECT 2 AS UID, 4 AS FID FROM DUAL),
(SELECT ID AS RID FROM role r WHERE Name = 'OWNER'));

But I got an error telling me "Every derived table must have its own alias". And I'm not quite sure how to solve that.

This query is in effect trying to add a link between the user table and the role table. User_ID is referencing the user table, and Role_ID the role table. I'm actually running this code through Ibatis, but when I couldn't get my code working, I tried the query manually in the Query Browser and saw that it didn't work there either. In my code I do have the user_id and friend_id, but not the role_id. This last is because I am using enums to store the roles and use the name. So what I want to do is look for the id belonging to the name.