mysql select query

Question

I've got 2 tables: interviews & interview_keywords.

An interview has 5 sorted keywords. I need a list of interviews with specified keywords in the right positions of the sorted list. This is what I've got so far, which isn't working:

   SELECT i.id, 
          i.title
     FROM interviews AS i 
LEFT JOIN interview_keywords AS ik ON i.id = ik.interview_id 
    WHERE i.cat_id = 1 
      AND ( (ik.keyword_id = 39 AND ik.sort = 1) 
        AND (ik.keyword_id = 33 AND ik.sort = 2) 
        AND (ik.keyword_id = 51 AND ik.sort = 3) 
        AND (ik.keyword_id = 96 AND ik.sort = 4) 
        AND (ik.keyword_id = 97 AND ik.sort = 5))

Nicolae Albu · Accepted Answer · 2011-07-16 17:43:20Z

3

SELECT i.id, i.title
FROM interviews i
INNER JOIN interview_keywords ik1
    ON ik1.interview.id = i.id
    AND ik.keyword_id = 39
    AND ik1.sort = 1
INNER JOIN interview_keywords ik2
    ON ik2.interview.id = i.id
    AND ik2.keyword_id = 33
    AND ik2.sort = 2
INNER JOIN interview_keywords ik3
    ON ik3.interview.id = i.id
    AND ik3.keyword_id = 51
    AND ik3.sort = 3
INNER JOIN interview_keywords ik4
    ON ik4.interview.id = i.id
    AND ik4.keyword_id = 96
    AND ik4.sort = 4
INNER JOIN interview_keywords ik5
    ON ik5.interview.id = i.id
    AND ik5.keyword_id = 97
    AND ik5.sort = 5
WHERE i.cat_id = 1

answered Jul 16, 2011 at 17:43

Nicolae Albu

1,2456 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql select query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related