I have two tables.
Table 1 opencall
Table 2 updatedb
The opencall table holds a list of call records with a unique reference for each call.
The updatedb table holds all the updates for the calls and the reference for the call for each update.
I want to join the updatedb to opencall whilst excluding calls that have been transferred to other departments.
When a call is assigned to another department the updatedb.updatetxt has a string like the following -
Call assigned to the ABCD-NL/SALES/OFFICE01/ group by DSTN (switched in group context)
Call assigned to the ABCD-NL/ADMINISTRATION/OFFICE01/ group by DSTN (switched in group context)
I can easily filter the rows, but I want to filter all the call updates fro the updatedb.
So far I have
FROM opencall o
JOIN updatedb u on o.callref = u.callref
o.status < 16
and not exists
From updatedb b
where o.callref = b.callref
and SUBSTRING(SUBSTRING_INDEX(b.updatetxt, '/', 2),30) in ('SALES','ADMIN'))
I am not entirely confident of my statement, and would appreciate it someone can help.