title: Search Engine with Multiple Qualification Filters in SQL Server
body: <p>I am trying to build a search engine like the ones Job portals have. My query is particularly related to qualification. My end user may query for employees who have a single or set of qualification-discipline combinations, or they may give a list of qualifications with disciplines and ask for all employees who have done any of the given qualification-discipline combinations. My script is as follows.</p>
  <pre><code>declare @Qualification table(QualId int identity primary key, Qualification varchar(100))
  declare @Discipline table(DispId int identity primary key, Discipline varchar(100))
  declare @Emp table(Eid int identity primary key, EName varchar(100))
  declare @EmpQualification table(Eid int, QualId int, DispId int)
  declare @ReqData table(QualId int, DispId varchar(100))
  declare @AllMandatory char(1)
  
  insert into @Qualification
  select 'Diploma' union
  select 'B.E' union
  Select 'MBA' union
  select 'MCA' union
  Select 'B.Sc' union
  Select 'B.Com'
  
  insert into @Discipline
  select 'HR' union
  select 'IT' union
  Select 'Computers' union
  select 'Agriculture' union
  Select 'Civil'
  
  insert into @Emp
  select 'SANTHOSHANAND M' union
  select 'VENKATA VEERA JAGADEESH MANNE' union
  select 'E.SURESH KUMAR' union
  select 'E HARIHARA PUTHIRAN' union
  select 'SOUMYAJIT MOHANTY' union
  select 'TANMOY DAS' union
  select 'PANAKANTI SANTHOSH' union
  select 'G NAVEEN KUMAR' union
  select 'VIJAY LOGANATHAN' union
  select 'SAI KIRAN GANDHE'
  
  INSERT INTO @EmpQualification
  SELECT 1,1,3 UNION
  SELECT 1,5,4 UNION
  SELECT 2,2,2 UNION
  SELECT 2,6,3 UNION
  SELECT 3,3,3 UNION
  SELECT 3,2,3 UNION
  SELECT 4,4,2 UNION
  SELECT 4,2,3 UNION
  SELECT 5,5,4 UNION
  SELECT 5,3,1 UNION
  SELECT 6,6,3 UNION
  SELECT 6,4,3 UNION
  SELECT 7,1,3 UNION
  SELECT 7,5,5 UNION
  SELECT 8,2,3 UNION
  SELECT 8,6,3 UNION
  SELECT 9,3,1 UNION
  SELECT 9,1,3 UNION
  SELECT 10,4,5 UNION
  SELECT 10,2,5 
  
  SELECT e.Eid, E.EName Name, ql.Qualification, d.Discipline FROM @Emp E
  INNER JOIN @EmpQualification Q ON E.Eid = Q.Eid
  INNER JOIN @Qualification QL ON Q.QualId = QL.QualId
  INNER JOIN @Discipline D ON Q.DispId = D.DispId
  
  insert into @ReqData
  select 1,'Any' union
  select 5,4
  
  set @AllMandatory = 'N'
  
  if @AllMandatory = 'N'
  select * from @Emp where Eid in (
  select distinct q.Eid from @EmpQualification q
  inner join (select distinct QualId, convert(int,DispId) DispId from @ReqData where DispId &lt;&gt; 'Any') f on q.QualId = f.QualId and q.DispId = f.DispId
  union
  select distinct q.Eid from @EmpQualification q
  inner join (select distinct QualId from @ReqData where DispId = 'Any') f on q.QualId = f.QualId)
  --else
  -- My recursive procedure goes here, which consumes lot of time
  </code></pre>
  <p>Sample query of my users is as follows.</p>
  <p><a href="https://i.stack.imgur.com/F1PJY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/F1PJY.png" alt="enter image description here" /></a></p>
  <p>This will come in my <code>@ReqData</code> table as below</p>
  <p><a href="https://i.stack.imgur.com/URbmi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/URbmi.png" alt="enter image description here" /></a></p>
  <p>All employees who meet all the mentioned criteria. This is captured through my <code>@AllMandatory</code> variable</p>
  <pre><code>set @AllMandatory = 'Y'
  </code></pre>
  <p>The above query should give me all the employees who did B.Com with any discipline and also did their MBA in HR. Following should be the query result</p>
  <pre><code>Eid EName
  1   E HARIHARA PUTHIRAN
  </code></pre>
  <p>Assume that with the same <code>@ReqData</code> table, if <code>@AllMandatory</code> is set to <code>N</code>, the result should be as follows, which means all employees who have done either B.Com with any discipline or all employees who have done MBA HR.</p>
  <pre><code>Eid EName
  1   E HARIHARA PUTHIRAN
  5   SAI KIRAN GANDHE
  7   SOUMYAJIT MOHANTY
  9   VENKATA VEERA JAGADEESH MANNE
  </code></pre>
  <p><code>@ReqData</code> may sometimes come with 50 to 60 rows.
  To achieve this I am only able to do with looping or stored procedure recursion. I have around 50000 employees and around 2,00,000 qualifications which keep growing too. So, this looping and recursion are making my system slow. Is there a better and easier way to solve this? Is it actually possible to handle it with a single query?</p>