Looping through Where statement until a result is found (SQL)

user_8967792 0
Viewed 128

Problem Summary:

I'm using Python to send a series of queries to a database (one by one) from a loop until a non-empty result set is found. The query has three conditions that must be met and they're placed in a where statement. Every iteration of the loop changes and manipulates the conditions from a specific condition to a more generic one.

Details:

Assuming the conditions are keywords based on a pre-made list ordered by accuracy such as:

Option KEYWORD1   KEYWORD2   KEYWORD3 
  1     exact      exact      exact     # most accurate!
  2     generic    exact      exact     # accurate
  3     generic    generic    exact     # close enough
  4     generic    generic    generic   # close
  5     generic+   generic    generic   # almost there
  .... and so on.

On the database side, I have a description column that should contain all the three keywords either in their specific form or a generic form. When I run the loop in python this is what actually happens:

-- The first sql statement will be like

Select * 
From MyTable
Where Description LIKE 'keyword1-exact$' 
  AND Description LIKE 'keyword2-exact%' 
  AND Description LIKE 'keyword3-exact%'

-- if no results, the second sql statement will be like 

Select * 
From MyTable
Where Description LIKE 'keyword1-generic%' 
  AND Description LIKE 'keyword2-exact%' 
  AND Description LIKE 'keyword3-exact%'

-- if no results, the third sql statement will be like 

Select * 
From MyTable
Where Description LIKE 'keyword1-generic%' 
  AND Description LIKE 'keyword2-generic%' 
  AND Description LIKE 'keyword3-exact%'

-- and so on until a non-empty result set is found or all keywords were used

I'm using the approach above to get the most accurate results with the minimum amount of irrelevant ones (the more generic the keywords, the more irrelevant results will show up and they will need additional processin)

Question:

My approach above is doing exactly what I want but I'm sure that it's not efficient.

What would be the proper way to do this operation in a query instead of Python loop (knowing that I only have a read access to the database so I can't store procedures)?

sql-like sql-server sql python
3 Answers

Here is an idea

select top 1
    * 
from
(
    select
        MyTable.*,
        accuracy = case when description like keyword1 + '%'
            and description like keyword2 + '%'
            and description like keyword3 + '%'
        then accuracy
        end
    -- an example of data from MyTable
    from (select description = 'exact') MyTable
    cross join      
    (values 
        -- generate full list like this in python 
        -- or read it from a table if it is in database
        (1, ('exact'), ('exact'), ('exact')),
        (2, ('generic'), ('exact'), ('exact')),
        (3, ('generic'), ('generic'), ('exact'))
    ) t(accuracy, keyword1, keyword2, keyword3)
) t
where accuracy is not null
order by accuracy
user_3172500

I would not do a loop over database queries. Instead I would search for the least specific, i.e. most generic, keyword and return all these rows.

Select * 
From MyTable
Where Description LIKE '%iPhone%'

This returns all the rows with iPhones. Now do the further processing, i.e. find the best match, in memory. This is much faster than multiple queries.

If you have several equally most generic keywords, then query them with OR

Select * 
From MyTable
Where Description LIKE '%iPhone%' OR
      Description LIKE '%i-Phone%'

But any case make only one query.

user_8809900

Please try using RegEx regular expression functionality of Sql server. Or else you can try impoting re in python for regular expression. First you can collect the data and then try re to achieve you r goal. Hope this is helpful.

user_88610830
Related