Codes represent different skills that people might have, like:
Technology-Chief Executive Officer
people, clearly holds data about people.
CodedPeople holds data on which people in the db are coded what skills.
What I need is a query that returns all distinct people records and takes a number of codeNames as input.
So if I have person1 and person2 in the db and they are coded as:
Person1 is a t-Founder
Person1 is a t-CEO
Person2 is a t-Systems Designer
Query 1, looking for all t-CEO
Result 1: person1
Query 2, looking for all t-CEO AND t-Founder
Result 2: person1
Query 3, looking for all t-CEO OR t-Systems Designer
Result 3: person1, person2
Query 4, looking for all t-Founder AND t-Systems Designer
Result 4: -
SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' ) )
ORDER BY peopSName, peopFName
And I add in parameters from my front end to the WHERE clause, but that only works with a single parameter really.