How to make a query on multiple columns without writting all possible permutations

execution-planoracleperformanceplsql

I'm at my first question on stackexchange, as i have a few days since I struggle on this matter:

I want to make a complex query(PLSQL) on a table that has col1,col2,col3,col4,col5 having values like (names: which are split- one part per column)

  • Andrew Joan Bach Mike
  • Mark Andrew Livy
  • Joan Arch Donnie
  • Joan Andrew Lyx

Number of parts differ from 1 to 5.

I want to search in different combinations like this:

  1. Bach Joan Mike – get #1, #3,#4 order
  2. Andrew Bach – get the following order #1,#2,#4 order

I don't like the idea of using a looooong query in which I will write all possible permutations in order to mach each part of my search string

What I'd like to achieve is this:

  1. first set: match all those n parts (both Bach and Joan and Bach match, in any order)
  2. second set: match n-1 parts (at least N-1 of my search parts match the row, in any order)
  3. third set: match n-2 parts

I use a ORACLE database and I was thinking on making it in a stored procedure: match_my_set(query_str,col1,col2,col3,col4,col5). I would write at least 5 loops (loop into loop) in order to achieve this, but I doubt it is a professional idea . .

Any help is appreciated. Thank you

Best Answer

You don't need loops, but one CASE per searched value:

select ...
from
 ( 
   select ...
      case when 'Bach' IN (col1,col2,col3,col4,col5) then 1 else 0 end +
      case when 'Joan' IN (col1,col2,col3,col4,col5) then 1 else 0 end +
      case when 'Mike' IN (col1,col2,col3,col4,col5) then 1 else 0 end as matches
   from tab
 ) dt
where matches > 0
order by matches desc
Related Question