• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 54
  • Last Modified:

SQL Server query to look for te best possible match for a search

I have table called PRODUCTDATA which has the following data
PRODUCTDATA Table
I'm querying PRODUCTDATA from UI by using a combination of search string  for example if a user searches  using P1,ET,ML  then the  SQL query should search in all columns for possible combinations of P1,ET,ML in any order then it should fetch 20 as Product Units however if a user searches table using TT,KK,ET , SQL query should search all columns for all possible combinations and find the best possible match like ET is present so should fetch 26 as product Units .
Please suggest a way to handle this
0
ronan_40060
Asked:
ronan_40060
  • 5
  • 3
2 Solutions
 
Nitin SontakkeDeveloperCommented:
It is rather preferred that you post your mock data in script / text format so that it can readily be used by the experts while formulating a solutions. Images don't really help much here.

Furthermore, you should also mention the version of the SQL Server you are using. In absence of one, most expert would assume that it is the latest which you may not be targeting.

In any case, here is script I came up with loads of gymnastics, may be there experts out there would probably give you more concise and elegant solution:

declare @productdata table
(
   [product1] varchar(5)
  ,[product2] varchar(5)
  ,[product3] varchar(5)
  ,[productunits] varchar(5)
)

insert into @productdata values
 ('P1', null, null, 10)
, ('P1', 'ET', null, 12)
, ('ET', 'P1', 'ML', 20)
, (null, 'ML', 'P1', 15)
, (null, null, 'P1', 8)
, (null, null, 'ET', 26)

declare @searchTerm varchar(100) = /*'TT,KK,ET' --*/ 'P1,ET,ML'

select top 1 [productunits], sum(iif([product1] = [value], 1, 0)) [matched], sum(iif([product1] is null, 1, 0)) [nullcount]
from (
  select [productunits], [product1]
  from @productdata
  union all
  select [productunits], [product2]
  from @productdata
  union all
  select [productunits], [product3]
  from @productdata
) pd
cross join (select [value] from string_split(@searchTerm, ',')) st
group by [productunits]
having sum(iif([product1] = [value], 1, 0)) != 0
order by 2 desc, 3 desc

Open in new window

0
 
ronan_40060Author Commented:
Thanks Nitin for our time and solution however my solution should include only SQL query and should work in SQL Server 2016 also on Oracle 11 g
0
 
Nitin SontakkeDeveloperCommented:
It is only SQL Query. Much of it can easily be converted to Oracle, I believe.

Except probably the part where a comma delimited string which needs to be converted to a table. I am almost certain there will be ways to do it in Oracle, (Example: http://www.dba-oracle.com/t_extract_comma_delimited_strings_oracle_sql.html)

Or you mean an Identical query which should work BOTH in MS SQL Server and Oracle?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ronan_40060Author Commented:
yes , the plain SQL query should work in both SQL Server and Oracle like
select Productunits
from PRODUCTDATA
where 'ML' in (product1, product2, product3) and
      'P1' in (product1, product2, product3) and
      (product1 in ('ML', 'P1') or product1 is null) and
      (product2 in ('ML', 'P1') or product2 is null) and
      (product3 in ('ML', 'P1') or product3 is null) ;

but above query  works in case if ML and P1 is used as a search criteria but it doesn't work in case if ML is NOT present in the table in such case  it should search for either ML or P1 only
0
 
Nitin SontakkeDeveloperCommented:
Understood. However, we have to also how exactly is this going get constructed and executed too! What parameters will be sending? Will there be 3 separate values?

May be it is easier to get an output just by executing a given query, but it should fit the actual executing environment, isn't it? Being developer, I always think from that point, which I believe is important. Unless of course it is an interview question, then it is a different story altogether.

So please clarify. Is sql something get constructed in application code dynamically and issued to db? Or what?
0
 
ronan_40060Author Commented:
hi Nitin

Thanks for your reply , this query gets created dynamically and gets called from an Java application UI that has search fields , please note that the PRODUCTDATA table wont be a huge , it contains almost 50 rows so performance is not an issue also a user can enter 1 search or 2 search or 3 search criteria , abve example I have given with 3 search criteria but it can also be 2 or 1 search .
Please let me know your thoughts
0
 
Scott PletcherSenior DBACommented:
The logic is straightforward enough, but making exactly the same query work in SQL and Oracle is not quite as straightforward, but it works if you're on reasonably recent versions of SQL:

SELECT PRODUCTUNITS
FROM @productdata
WHERE product1 IN ('ML', 'P1')
   OR product2 IN ('ML', 'P1')
   OR product3 IN ('ML', 'P1')
ORDER BY
    CASE WHEN product1 IN ('ML', 'P1') THEN 1 ELSE 0 END +
    CASE WHEN product1 IN ('ML', 'P1') THEN 1 ELSE 0 END +
    CASE WHEN product1 IN ('ML', 'P1') THEN 1 ELSE 0 END DESC,
    CASE WHEN product1 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN product2 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN product3 IS NULL THEN 1 ELSE 0 END DESC
OFFSET 0 ROWS
FETCH FIRST 1 ROWS ONLY
1
 
ronan_40060Author Commented:
Thank you , I will revert on this , again thank you all for our solution and time
0
 
ronan_40060Author Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now