Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Unanswered: select candidates with more than one skill (was "Help required")

    I am struggling with a query and need urgent help.

    I have a table with fields

    canid
    skillLevel
    percent
    date

    and example of the results wud be

    canid skillLevel Percent Date
    704 1 20 n/a
    705 2 10 n/a
    775 1 50 n/a


    what i want to do is produce a query that would return a result from the following search critera

    return all results that match

    skilllevel 1 and 20 percent AND skillLevel 2 and 10 percent

    I tried this below but obviously im doing something wrong

    select * from simulatedHistory where
    ((skilllevel = 1 and percent = 20) And (skilllevel = 2 and percent = 10))


    I need the query returning

    canid skillLevel Percent Date
    704 1 20 n/a
    705 2 10 n/a

    any suggestions??? any help wud be much appreciated

  2. #2
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    select *
    from simulatedHistory
    where
    ((skilllevel = 1 and percent = 20)
    OR
    (skilllevel = 2 and percent = 10))

  3. #3
    Join Date
    Jan 2005
    Posts
    5
    Thanks for the reply, but thats not what i need, i need it to match Both statements not either or, sorry for not making sense in the initial thread.


  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Erm...How can a row have skilllevel equal to both 1 and 2?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why, in the Universe of Crazy World, of course!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2005
    Posts
    5

    Smile

    skilllevel 1 - easy
    skilllevel 2 - intermediate
    skilllevel 3 - advanced

    basically they are taking a test with different levels.

    i want to produce a report that will return a results

    say return me all the candidates that have scored
    skillevel 1 = 80%
    skillevel 2 = 30%

    so if these both queries match i want it to return the candidates that match both queries.!

    hope this makes sense!

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Can we assume "canid" is an identifier for a person or test taker? If so, then:

    Code:
    select * 
    from simulatedHistory a join simulatedHistory b on a.canid = b.canid
    where
    ((a.skilllevel = 1 and a.percent = 20) And (b.skilllevel = 2 and b.percent = 10))
    may be closer to what you need.

  8. #8
    Join Date
    Jan 2005
    Posts
    5
    Cheers Mate this kinda does the trick!!!! not exactly the result i was looking for, i thought it would return a record for each matched statement, but it returns one canid, but i can do something with this! cheers

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mcrowley is close

    since you're searching for the existence of two separate rows, grouping by candidate is required
    Code:
    select canid
      from simulatedHistory 
     where skilllevel = 1 and percent = 20
        or skilllevel = 2 and percent = 10
    group
        by canid
    having count(*) > 1
    this returns the candidates that have both criteria

    this solution is scalable to any number of criteria (whereas extending the self-join to three, four, five tables gets pretty tedious after a while)

    this solution also lends itself to similar but more complicated problems, such as "must have any three of the following five skills..."

    Last edited by r937; 01-18-05 at 16:22.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2005
    Posts
    5
    Works a Treat, thanks for all your help people, very much appreciated!


Posting Permissions

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