Interesting mySQL (1 Viewer)

dt01pqt

Certified
Local time
Today, 15:56
Joined
Mar 22, 2004
Messages
271
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:D
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:rolleyes:

It is fortunate that you have other options for 4.0 because it doesn’t support either derivative tables or subselects
 
Last edited:

Users who are viewing this thread

Top Bottom