blonde to write query or design fault

Dear All,

Im wondering if its the design that needs to be changed or I simply cant 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 of people, example the sort of job functions theyve held in their employment. Like:

t-CEO,
t-CFO
t-Founder
etc.

people, clearly holds data about people.

CodedPeople holds data about which people are coded. So person1 can be coded as t-CEO as well t-Founder, and person2 coded as t-CFO

What I need is a query that returns all distinct people records and takes a number of codeNames as input. So if I throw in t-CEO OR t-Founder I get person1, again if I define t-CEO AND t-Founder I get person1.

However when I add t-CEO OR t-CFO I get person1 and person2 but when the query takes t-CEO AND t-CFO I get no result.

I cant seem to come up with anything that would give me a good starting point. Is there a design fault here? All opinions are much appreciated, thanks in advance!"the query takes t-CEO AND t-CFO I get no result."

Is that wrong? No person is t-CEO AND t-CFO in your example.

Please tell us what you want the result to be.|||Thanks for getting back!

Ok, so I have 3 codes and 2 people in the database. (In reality its about 250 different codes and about 10,000 people, growth is about 5000 / year)

I coded person1 as a technology-Chief Executive Officer and also as a technology-Founder (t-CFO, t-Founder)

I also coded person2 as a technology-Chief Financial Officer (t-CFO)

I want to write a query that takes codes as parameters:

t-CEO AND t-Founder = returns person1 (as hes coded as a t-CEO and t-Founder)
t-CEO OR t-CFO = returns person1 and person2 (as person1 is coded as t-CEO and person2 is coded as t-CFO)

t-CEO AND t-CFO = returns no result ( as no person in the db is coded as both a t-CEO and also a t-CFO)

t-CFO or t-Founder = returns person1 and person2 (as person1 is coded as a t-Founder and person2 is coded as a t-CFO)

Am I describing it correctly?

Of course the query need to be flexible as Ill using it from ASP.NET dropping in the parameters so codeName Like t-CEO OR codeName Like t-Founder

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

But thats useless!|||Let me correct that, I have mixed up the similar codes of t-CEO and t-CFO, the correct one is:

Thanks for getting back!

Ok, so I have 3 codes and 2 people in the database. (In reality its about 250 different codes and about 10,000 people, growth is about 5000 / year)

I coded person1 as a technology-Chief Executive Officer and also as a technology-Founder (t-CEO, t-Founder)

I also coded person2 as a technology-Chief Financial Officer (t-CFO)

I want to write a query that takes codes as parameters:

t-CEO AND t-Founder = returns person1 (as hes coded as a t-CEO and t-Founder)
t-CEO OR t-CFO = returns person1 and person2 (as person1 is coded as t-CEO and person2 is coded as t-CFO)

t-CEO AND t-CFO = returns no result ( as no person in the db is coded as both a t-CEO and also a t-CFO)

t-CFO OR t-Founder = returns person1 and person2 (as person1 is coded as a t-Founder and person2 is coded as a t-CFO)

Am I describing it correctly?

Of course the query need to be flexible as Ill using it from ASP.NET dropping in the parameters so codeName Like t-CEO OR codeName Like t-Founder

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

But thats useless!