Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    92

    Unanswered: blonde to write query or design fault

    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 of people, example the sort of job functions theyíve 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 canít 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!

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    "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.

  3. #3
    Join Date
    Mar 2005
    Posts
    92
    Thanks for getting back!

    Ok, so I have 3 codes and 2 people in the database. (In reality itís 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 heís 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 Iíll 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 thatís useless!

  4. #4
    Join Date
    Mar 2005
    Posts
    92
    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 itís 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 heís 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 Iíll 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 thatís useless!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •