CODEWITHSUNDEEP
mysqlsql
user745639
user745639

Reputation: 13

mysql select query

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))

Upvotes: 1

Views: 91

Answers (1)

Nicolae Albu
Nicolae Albu

Reputation: 1245

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

Upvotes: 3

Related Questions