Dear All,

I’m wondering if it’s the design that needs to be changed or I simply can’t put this together.

I have 3 tables.

1. people (peopId, peopFName, peopSName etc.)
2. codes (codeId, codeName)
3. codedPeople(codePeopleId, peopId, codeId)

Codes represent different skills that people might have, like:

Technology-Founder
Technology-Chief Executive Officer
Technology-Systems Designer
etc.

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: -


I have:


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.

Any idea how to implement what I need to achieve?