SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT if multiple entries exist in lookup table

    Hi all,

    I'm working on an IT recruitment database.

    My particular problem involves searching for candidates according to their skills.

    I have a table for candidates and a table for edit_qualifiers (skills). You can associate an unlimited number of Edit Qualifiers with a Candidate. Therefore, for every Edit Qualifier that applies to a Candidate, a create a row within a lookup table (candidates_edit_qualifiers_lookup) containing: id, candidate, edit_qualifier.

    What I need to achieve is a search query that selects a Candidate from the candidates table only if they have every Edit Qualifier that has been specified within the search.

    My current attempt:
    Code:
    SELECT * FROM candidates LEFT JOIN candidates_edit_qualifiers_lookup ON candidates_edit_qualifiers_lookup.candidate = candidates.id WHERE  candidates_edit_qualifiers_lookup.edit_qualifier = '4' AND candidates_edit_qualifiers_lookup.edit_qualifier = '5'  GROUP BY candidates.id ORDER BY surname ASC, name ASC LIMIT 0, 20
    I know that something is fundamentally wrong with this, but I don't know if the solution lies in a different type of join or perhaps different table structure altogether?

    Thanks in advance,

    spitfireweb

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT candidates.* 
      FROM candidates 
    INNER
      JOIN ( SELECT candidate
               FROM candidates_edit_qualifiers_lookup 
              WHERE edit_qualifier IN ('4','5') 
             GROUP 
                 BY candidate
             HAVING COUNT(*) = 2 ) AS these
        ON these.candidate = candidates.id 
    ORDER 
        BY surname ASC
         , name ASC 
    LIMIT 0, 20
    the number in the HAVING clause will be the number of listed edit qualifiers that they must have
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perfect. That's exactly what I needed.

    This is the best forum response I've ever had - exceptional.

    Thank you very much,
    spitfireweb


Tags for this Thread

Bookmarks

Posting Permissions

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