can't get fried brain around this query...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

can't get fried brain around this query...

Author

Topic

Danny__T
Starting Member

27 Posts

Posted - 2004-09-14 : 13:39:32

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-14 : 13:47:13

what if factor2 of the candidates matches factor 1 of the job criteria, or factor 2 matches factor 3, etc? is that OK? (in either the ALL or SOME scenerios)

This query is difficult at this point because of the way your database is set up. You should really normalize it and store the factors in a related table, 1 factor per row.

- Jeff

Danny__T
Starting Member

27 Posts

Posted - 2004-09-14 : 14:48:03

I see your point, the db is evolving from changing requirements so perhaps i'm trying to hammer a square peg into a round hole at the moment. Factors 1,2 and 3 are completely seperate so am only looking for matches of 1-1, 2-2, 3-3 (I confuse myself over the a,b and c - basically if a candidate's factor3 field matches the job criteria 3a or 3b or 3c)

so normalised would be (I think):

tblCandidate
candidateId

tblCandidateFactor
candidateId
FactorId

tblFactor
FactorId
Factor1
Factor2
Factor3

tblJobCriteriaFactor
jobId
FactorId

tblJobCriteria
jobId
matchAll

Not sure if this helps at all, i'll try to think of a better way of explaining it

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-14 : 15:02:48

that's not quite it, but in this case it shouldn't matter much.

you basically spelled out the WHERE clause yourself -- did you try to implement it? you had all the components, you just need to put them together.

by the way -- Aliases and the IN() clause can be two of your greatest allies in writing readable code:


SELECT  {column list here}
FROM
   tblCandidate C
INNER JOIN
   tblJobCriteria JC
ON
    (JC.matchAll  = 1 AND 
    C.factor1 = JC.factor1 AND 
    C.factor2=JC.factor2 AND 
    C.factor3 IN (JC.factor3a, JC.factor3b, JC.factor3c)
    )

    OR

    ((JC.matchAll=0) AND 
     (C.factor1 = JC.factor1 OR 
      C.factor2 = JC.factor2 OR 
      C.factor3 IN (JC.factor3a, JC.factor3b, JC.factor3c)
     )
    )

- Jeff

Danny__T
Starting Member

27 Posts

Posted - 2004-09-15 : 00:13:49

Cheers Jeff thats a big help, thats almost there, but what i need is for IF matchAll = 1 to only select options where ALL the factors are matched, otherwise select if ANY factors are matched. I may be doing something drastically wrong but that seems to select if they all match OR just some match. Psudo SQL follows:

SELECT {column list here}
FROM
tblCandidate C
INNER JOIN
tblJobCriteria JC
ON
IF(JC.matchAll = 1) THEN
C.factor1 = JC.factor1 AND
C.factor2=JC.factor2 AND
C.factor3 IN (JC.factor3a, JC.factor3b, JC.factor3c)
)

ELSE

((JC.matchAll=0) AND
(C.factor1 = JC.factor1 OR
C.factor2 = JC.factor2 OR
C.factor3 IN (JC.factor3a, JC.factor3b, JC.factor3c)
)
)

But yes aliases and IN will be extremely useful, thanks very much for your help

Danny__T
Starting Member

27 Posts

Posted - 2004-09-15 : 02:14:16

I believe I have solved this by using a union join such as: -

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))

Is there any problems with doing it like this that I should be aware of? seems to bring back what i need.

Thanks for your help

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-15 : 07:53:59

did you even TRY what I posted, or did you just look at it and assume it wouldn't work?

If you say:

select *
from SomeTable where ConditionA
union
select *
from SomeTable where ConditionB

isn't that equivalent to :

select *
from SomeTable
where ConditionA OR ConditionB

??

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources