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

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

Select allOpen in new window

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

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

Thank you , I will revert on this , again thank you all for our solution and time

Thank you