Hello everyone! First post here and hoping to gain some guidance. I'm attempting to insert records based on data from three separate tables. The join conditions are ... odd to say the least. Here's the gist of what I'm trying to do ( I know I cannot use an insert statement this way, I'll get to how I'm trying to re-work here shortly).
Code:
insert into i_m
(i_id, m_id)
(
(select i_id
from i, sheet1
where i_id not in (select i_id from i_m)
and mid(i.m_number, 5) = left(sheet1.name, itr(1, sheet1.name, '.')-1)
)
(select m_id
from m
where m_id not in (select m_id from i_m)
)
)
The sheet1 table has filenames that correspond with a value in i.m_number. The file names look like "1111.txt" and the i.m_number field looks like "txt 1111" hence the mid, left, and InStr functions.
This is about as far as I have gotten with the rest of the query as a select statement in an attempt to just get a look at the dataset. Most of my problem with this query is that it takes a VERY long time to run (10 minutes). I can compact & repair the database, but that isn't a maintainable solution to the issue.
Code:
select i_id, m_id from i, m, sheet1
where i_id not in (select i_id from i_m)
and mid(m_number, 5) = left(sheet1.name, itr(1, sheet1.name, '.')-1)
and m_id not in (select m_id from i_m)
I've also tried working it with joins, but couldn't seem to logic my way through the rest:
Code:
select i_id, m_id from m
inner join sheet1
on mid(m_number) = left(sheet1.name, itr(1, sheet1.name, '.')-1)
inner join i
on i id not in (select i_id from i_m)
I have a feeling there is some kind of nested inner join I need to do, but I'm not sure how to make it work. Any help with this would be greatly appreciated.