Link to home
Start Free TrialLog in
Avatar of ronan_40060
ronan_40060Flag for United States of America

asked on

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

I have table called PRODUCTDATA which has the following data
User generated image
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
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

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

Avatar of ronan_40060

ASKER

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
SOLUTION
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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?
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you , I will revert on this , again thank you all for our solution and time
Thank you