Not Exists to filter all rows

Hi,

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

SELECT *
FROM opencall o
JOIN updatedb u on o.callref = u.callref
where 
o.status < 16
and not exists
(SELECT *
From updatedb b
where  o.callref = b.callref
and SUBSTRING(SUBSTRING_INDEX(b.updatetxt, '/', 2),30) in ('SALES','ADMIN'))

Open in new window



I am not entirely confident of my statement, and would appreciate it someone can help.

Thank you.
dan_stanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Well, because you are doing an inner join on updateb (from opencall) then only those rows that match the "ON" condition will be found.

That (to me) indicates the "Not Exists" is not needed and could be simply expressed as part of the "where" condition.

SELECT *
FROM opencall o
JOIN updatedb u on o.callref = u.callref
where o.status < 16
and SUBSTRING(SUBSTRING_INDEX(u.updatetxt, '/', 2),30) NOT in ('SALES','ADMIN')

Open in new window


Then there is the substring...

The substring_index you are using is saying "give me everything to the left of the second '/'" So, I am not so sure that you are actually extracting just the "department"

Could also be achieved with a "like"

e.g.
...
where o.status < 16
and not ( u.updatetxt like '%/SALES/%' or u.updatetxt like '%/ADMINISTRATION/%')

Open in new window


So, you might want to explore that part a bit more first... Which you can do by trying it in its own select and see what rows you end up with...
0
Mark WillsTopic AdvisorCommented:
Oh,

If your intent is to exclude EVERY callref from updateb that has at least one row containing SALES or ADMIN for a callref then the NOT EXISTS and be used to good affect.

But could also get a list of callref's which has those strings and use the callref's as "NOT IN"

Guess it depends on how many exist, and if that is what you are trying to do...

For example, how do you really tell if it is now a different department ? What are you really comparing against to see if the department has changed ? Should we be counting the number of departments to see if there is more than one ?

Maybe some "dummy" data to exemplify your case...
0
dan_stanAuthor Commented:
Hi,

Sorry for the late response.

I need to filter out all the rows for every matching call reference in the update table if one row contains the string indicating it has been assigned to another department.

My statement appears to be working but I'm still unsure what to use, not in or not exists.
0
Mark WillsTopic AdvisorCommented:
Well, if you want to exclude any call (from opencall) that has been assigned to SALES or ADMIN (at any stage regardless of what else might be there in updateb), then the NOT EXISTS is probably the easiest way to go.

But given the text looks like :

'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)'

Then your substring is going to give different results to what you are checking for.

The NOT IN (in place of not exists) would mean you extract all the callref that exists in updateb with that criteria. But also gives the opportunity to simply extract every callref rather than checking just for that given callref. If there isn't too many, then that will work fine too...

Which also leads into a third way and could be more economical.

SELECT *
FROM opencall o 
JOIN updatedb u on o.callref = u.callref
LEFT OUTER JOIN (SELECT callref
                 From updatedb b
                 where  SUBSTRING(SUBSTRING_INDEX(b.updatetxt, '/', 2),30) in ('SALES','ADMIN')) x
                 on o.callref = x.callref 

where  o.status < 16
and    x.callref is NULL

Open in new window


it would mean that the "x" subquery is resolved first for the entire subquery rather than each individual callref being checked with another query in the where clause.

When you have a query that depends on the column from opencall that becomes a correlated subquery and can be expensive in terms of performance. So always worth checking the alternatives and keeping it "set based" queries if possible.

But also urge you to double check :

SELECT callref
From updatedb b
where  SUBSTRING(SUBSTRING_INDEX(b.updatetxt, '/', 2),30) in ('SALES','ADMIN')

Open in new window


to make sure that really does give you what you want, because at the moment it doesn't look at all correct. (possible because the query has been masked a bit, and that is up to you). Compared to the results from :

SELECT callref
From updatedb 
where  updatetxt like '%/SALES/%' or updatetxt like '%/ADMIN%'  

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.