Need to get the IDs for names based on 2 tables which are not linked-CodePudding

Below are the 2 table. Table1:

id   name
1    XYZ
2    ABC
3    POP



Table2:
id  dName
1    xyz_1
2    abc_1
3    pop_1
4    kkk_1


Need to fetch IDs for Table1.name = 'XYZ',
Need to fetch IDs for Table2.dName = 'xyz_1',
Need to fetch IDs for Table2.dName = 'abc_1'

this IDs will be inserted to Table3:

insert into Table3 (s_id,p_id,d_id) 
select Table1.id as s_id, Table2.id as p_id, Table2.id as d_id from Table1,Table2 where 
Table1.name = 'XYZ'
Table2.dName = 'xyz_1'
Table2.dName = 'abc_1'

Basically above select query should give the result like below. This should be the result of the above query I am expecting.

s_id     p_id        d_id
 1         1          2

Need help to define the proper query how I can get ids and directly insert ? No relationship between Table1 and Table2. Basically looking for single query which will give the result like above which I mentioned.

Thanks..

CodePudding user response：

The conditions 2 and 3 are independent, hence you must use independent table copies for these conditions:

SELECT t1.id s_id, t2.id p_id, t3.id d_id
FROM table1 t1
CROSS JOIN table2 t2
CROSS JOIN table2 t3
WHERE t1.name = 'XYZ'
  AND t2.dName = 'xyz_1'
  AND t3.dName = 'abc_1'