noob(ish) view help

• Dan Thomas

  SSC Enthusiast

  Points: 154

  More actions

  September 14, 2004 at 11:45 am

  #162626

  Hi, not quite sure how to achieve (or even explain) this but here goes: -

  I have 2 tables:

  tblCandidate | tblJobCriteria

  tblCandidate contains the following fields:

  candidateId

  factor1

  factor2

  factor3

  tblJobCriteria contains:

  jobId

  matchAll

  factor1

  factor2

  factor3a

  factor3b

  factor3c

  I want to create a view of candidates and jobs which matches the factors in tblCandidate to the factors in tblJobCriteria

  BUT if the tblJobCriteria.matchAll field is set to true then only match the candidates where:

  tblCandidate.factor1 = tblJobCriteria.factor1 AND tblCandidate.factor2=tblJobCriteria.factor2 AND tblCandidate.factor3 = (tblJobCriteria.factor3a OR tblJobCriteria.factor3b OR tblJobCriteria.factor3c))

  or if the matchAll field is set false then match where:

  tblCandidate.factor1 = tblJobCriteria.factor1 OR tblCandidate.factor2=tblJobCriteria.factor2 OR tblCandidate.factor3 = (tblJobCriteria.factor3a OR tblJobCriteria.factor3b OR tblJobCriteria.factor3c))

  any guidance is much appreciated

• Brandon Hunt

  Old Hand

  Points: 303

  More actions

  September 14, 2004 at 1:22 pm

  #523057

  Use a union.

  Try this:

  select c.CandidateID from tblCandidate c

  INNER JOIN tblJobCriteria jc ON

  jc.MatchAll = 1 AND (

  jc.factor1 = c.factor1

  AND jc.factor2 = c.factor2

  AND (jc.factor3a = c.factor3 OR jc.factor3b = c.factor3 OR jc.factor3c = c.factor3))

  UNION

  select c.CandidateID from tblCandidate c

  INNER JOIN tblJobCriteria jc ON

  jc.MatchAll = 0 AND (

  jc.factor1 = c.factor1

  OR jc.factor2 = c.factor2

  OR (jc.factor3a = c.factor3 OR jc.factor3b = c.factor3 OR jc.factor3c = c.factor3))

  HTH,

  
  
  -Brandon
  

• Dan Thomas

  SSC Enthusiast

  Points: 154

  More actions

  September 15, 2004 at 12:20 am

  #523102

  Quality mate, just what I needed,

  Kudos!

  Dan