Hope someone can help with this.
The idea currently is that I have a table for Candidates, and a table of Profiles, as well as a linking table CandidateProfiles, with just the tow ID fields.
The way it works is that when a new Candidate is added, various Profiles can be added to each one by checking a series of checkboxes, which are displayed using a loop.
I then have a search page where the same profiles can be searched by checking the boxes for the relevant profiles.
This then returns a results page, listing any matching candidates, which counts the matching profiles, and lists them in order of matching profiles.
The query that does that looks like this:
SELECT *, Count(*) As rank FROM Candidates, CandidateProfiles WHERE Candidates.CandidateID = CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(' . implode(',', $ckbox).') GROUP BY Candidates.CandidateID ORDER BY rank DESC, Candidates.CandidateID
My question is this:
How would I change that query so that it just returned the Candidates that matched ALL rather than ANY profiles?
i.e. the same search as above to just return:
Hope that makes sense, and any help much appreciated.