Nasty multi-row matching SQL

[kampfcoder]

I have a nasty problem, where I need to match all the values belonging to an object, with all the values belonging to another object.

Both object values point to the same table.

Only if ALL values match, will there be a hit.


I have written an example below to clarify the problem. The table structure is identical to my problem's. I have only written field names, that are of interest to the problem.
Thanx in advance!

|
|

Tables:
++++++++++++++++++++++++++++
Profile
-------
profileid


profileeducation
----------------
profileid
educationid
++++++++++++++++++++++++++++


++++++++++++++++++++++++++++
candidate
---------
candidateid


candidateeducation
------------------
candidateid
educationid
++++++++++++++++++++++++++++



Example.
Two dataset:

Profile1 Candidate1
education1 education1
education2 education2
education3 education3

Only if all the profile's educations matches all the candidate's educations, will it return the candidate, and only ONE candidate
If the candidate has two educations, there will be no match. If the candidate has more educations than the profile, but 3 of them matches the profile, there will also be a match

 

[JarlH]

Do you mean you want just ONE candidate even if several different candidates match the education requirements? (Which one should be chosen?)

One possible solution:

SELECT * FROM candidate AS c
WHERE (SELECT COUNT(*) FROM profileeducation AS pe
WHERE pe.profileid = ?
AND pe.educationid NOT IN
(SELECT educationid FROM candidateeducation AS ce
WHERE ce.candidateid = c.candidateid)) = 0;


You can maybe also use some of (NOT) EXIST/SOME/ANY/ALL...

 

[kampfcoder]

No, I mean that each candidate has to be returned distinctly (I made a pathetic attempt, that returned one row per education, even when the candidate was the same). So it was more like a hint to use DISTINCT somewhere.

Sorry for the unclarity.

 

[r937]

you say "the profile" so presumably you will be searching for candidates using a WHERE clause for the profileid

select c.candidateid
     , c.candidatehatsize
  from candidate as c
 where c.candidateid 
    in ( 
       select ce.candidateid
         from profileeducation as pe
       inner
         join candidateeducation as ce
           on ce.educationid = pe.educationid 
        where pe.profileid = 937
       group
           by ce.candidateid
       having count(*)
            = ( select count(*)
                  from profileeducation 
                 where profileid = 937 )
       )

untested ;-)

r937.com | rudy.ca