Insert data using two select queries with different count of rows

I Wish to enter data into a third table while getting the data from the first two tables.In this case the first table is profile and second table is menu, and the third table is profile_menu. My objective is to map menus to a specific profiles.Practically one profile can have mapping to multiple menus. I am using the below query :

INSERT INTO profile_menu(item_id,profile_id) (
Select (select id_profile from profile where name = 'XYZ' and site = 46) as profile,
(SELECT  m.id_menu_item FROM menu m WHERE names IN ('menu1'
,'menu1'
,'menu2'
,'menu3'
,'menu4'
,'menu5')) as menus
from dual);

While running this i am getting “01427. 00000 – “single-row subquery returns more than one row”” this error. I Understand that i need the same rows in select of profiles but i am not getting how to acheive that.

If anybody can help.

Thanks.

Answer

If I understand, use join:

select p.id_profile, m.id_menu_item
from profile p cross join
     menu m 
where p.name = 'XYZ' and p.site = 46 and
      m.name in ('menu1' ,'menu1' ,'menu2' ,'menu3' ,'menu4' ,'menu5');

This will return multiple rows. That is presumably what you intend.