Simple SQL Query Or Design Problem?

Dear All

I have posted this a few days ago, but probably didn’t describe it properly so I’ll attempt it again.

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:

[ code]
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 ( parameter )
ORDER BY peopSName, peopFName
[ /code]

And I add in parameters from my front end to the WHERE clause, like in the example:

Parameter = ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' )


This works fine when using OR but doesn’t work when trying to use AND in a sense of a person is coded with skill1 AND skill2 AND skill3 etc.

Any idea how to implement what I need to achieve?