Not a question as such
I helped someone out with is a recruitment site where candidates have profiles and so do the vacancies. So there is a one to many relationship between Candidates and the Candidate Profiles and likewise with the Vacancies and the Vacancies Profiles.
So you basically have five tables.
Profiles (details about the profiles)
------
ID
Description...
Candidates
----------
CandidateID
Candidate Details...
CanditateProfiles
----------------
CandidateID
ProfileID
Vacancy
----------
VacancyID
Vacancy Details...
VacencyProfiles
----------------
VacancyID
ProfileID
The function of the system is to match the vacancies with the candidate that have the closest matching profiles by rank or even identify exact matches.
You can easily break it up into a couple of querys but I like to try for one
The ranked match is fairly easy something like
SELECT Candidates.CandidateID, snip, CandidateProfiles.ProfileID, VacancyProfiles.ProfileID, Count(*) As Rank FROM Candidates INNER JOIN CandidateProfiles ON Candidates.CandidateID = CandidateProfiles.CandidateID INNER JOIN VacancyProfiles ON VacancyProfiles.ProfileID=CandidateProfiles.ProfileID WHERE VacancyProfiles.VacancyID = VacancyID GROUP BY Candidates.CandidateID ORDER BY Rank DESC, Candidates.CandidateID
For the exact match I found out a really nifty way of doing it
SELECT Candidates.CandidateID, snip, CandidateProfiles.ProfileID, VacancyProfiles.ProfileID, Count(CandidateProfiles.ProfileID) As ra, Count(VacancyProfiles.ProfileID) As rb
FROM Candidates
INNER JOIN CandidateProfiles ON Candidates.CandidateID = CandidateProfiles.CandidateID
RIGHT JOIN VacancyProfiles ON VacancyProfiles.ProfileID=CandidateProfiles.ProfileID
WHERE VacancyProfiles.VacancyID = VacancyID GROUP BY Candidates.CandidateID HAVING ra = rb ORDER BY rb DESC, Candidates.CandidateID
It exploits the behaviour of COUNT which counts all rows except those with NULL. When you do a RIGHT JOIN of Vacancy Profiles There will be NULL where profiles don’t match exactly. HAVING ra = rb means that it will only show the exact matches.
Thing is it only works with mySQL 4.0 for some reason anything higher and it doesn’t. So with this guy I had to use
SELECT Candidates.CandidateID, snip, (SELECT Count(VacancyProfiles.ProfileID) FROM VacancyProfiles WHERE VacancyProfiles.VacancyID = VacancyID) As ra, Count(*) As rb
FROM Candidates
INNER JOIN CandidateProfiles ON Candidates.CandidateID = CandidateProfiles.CandidateID
RIGHT JOIN VacancyProfiles ON VacancyProfiles.ProfileID=CandidateProfiles.ProfileID
WHERE VacancyProfiles.VacancyID = VacancyID GROUP BY Candidates.CandidateID HAVING ra = rb ORDER BY rb DESC, Candidates.CandidateID
And this isn’t any faster than using two queries anyways
It is fortunate that you have other options for 4.0 because it doesn’t support either derivative tables or subselects
I helped someone out with is a recruitment site where candidates have profiles and so do the vacancies. So there is a one to many relationship between Candidates and the Candidate Profiles and likewise with the Vacancies and the Vacancies Profiles.
So you basically have five tables.
Profiles (details about the profiles)
------
ID
Description...
Candidates
----------
CandidateID
Candidate Details...
CanditateProfiles
----------------
CandidateID
ProfileID
Vacancy
----------
VacancyID
Vacancy Details...
VacencyProfiles
----------------
VacancyID
ProfileID
The function of the system is to match the vacancies with the candidate that have the closest matching profiles by rank or even identify exact matches.
You can easily break it up into a couple of querys but I like to try for one
The ranked match is fairly easy something like
SELECT Candidates.CandidateID, snip, CandidateProfiles.ProfileID, VacancyProfiles.ProfileID, Count(*) As Rank FROM Candidates INNER JOIN CandidateProfiles ON Candidates.CandidateID = CandidateProfiles.CandidateID INNER JOIN VacancyProfiles ON VacancyProfiles.ProfileID=CandidateProfiles.ProfileID WHERE VacancyProfiles.VacancyID = VacancyID GROUP BY Candidates.CandidateID ORDER BY Rank DESC, Candidates.CandidateID
For the exact match I found out a really nifty way of doing it
SELECT Candidates.CandidateID, snip, CandidateProfiles.ProfileID, VacancyProfiles.ProfileID, Count(CandidateProfiles.ProfileID) As ra, Count(VacancyProfiles.ProfileID) As rb
FROM Candidates
INNER JOIN CandidateProfiles ON Candidates.CandidateID = CandidateProfiles.CandidateID
RIGHT JOIN VacancyProfiles ON VacancyProfiles.ProfileID=CandidateProfiles.ProfileID
WHERE VacancyProfiles.VacancyID = VacancyID GROUP BY Candidates.CandidateID HAVING ra = rb ORDER BY rb DESC, Candidates.CandidateID
It exploits the behaviour of COUNT which counts all rows except those with NULL. When you do a RIGHT JOIN of Vacancy Profiles There will be NULL where profiles don’t match exactly. HAVING ra = rb means that it will only show the exact matches.
Thing is it only works with mySQL 4.0 for some reason anything higher and it doesn’t. So with this guy I had to use
SELECT Candidates.CandidateID, snip, (SELECT Count(VacancyProfiles.ProfileID) FROM VacancyProfiles WHERE VacancyProfiles.VacancyID = VacancyID) As ra, Count(*) As rb
FROM Candidates
INNER JOIN CandidateProfiles ON Candidates.CandidateID = CandidateProfiles.CandidateID
RIGHT JOIN VacancyProfiles ON VacancyProfiles.ProfileID=CandidateProfiles.ProfileID
WHERE VacancyProfiles.VacancyID = VacancyID GROUP BY Candidates.CandidateID HAVING ra = rb ORDER BY rb DESC, Candidates.CandidateID
And this isn’t any faster than using two queries anyways
It is fortunate that you have other options for 4.0 because it doesn’t support either derivative tables or subselects
Last edited: