phpmysqlwordpress

MySQL query, how do I query this?


I have a query that needs to join 2 tables.

Activity table:

activity.id: 334
activity.user_id: 14
activity.type: quiz_evaluated
activity.item_id: 2968
activity.secondary_item_id: 3006
activity.date_recorded: 2016-05-25 15:07:14

Postmeta table:

postmeta.meta_id: 7856
postmeta.post_id: 3006
postmeta.meta_key: quiz_questions14
postmeta.meta_value: a:2:{s:4:"ques";a:4:{i:0;s:4:"2972";i:1;s:4:"2974"..

And here is what I need:

SELECT activity.item_id as course_id,
  postmeta.meta_value as quiz_data
FROM wp_bp_activity as activity
JOIN wp_postmeta as postmeta
  ON postmeta.post_id = activity.secondary_item_id
WHERE activity.type = 'quiz_evaluated'
  AND 'quiz_questions'+activity.user_id = postmeta.meta_key

But, the thing is that postmeta.post_id is not unique and I would need to confirm 2 variables, postmeta.post_id and postmeta.meta_key (which is 'quiz_question' + activity.user_id) to get a single line from database.

How may I achieve this?

PS.: I'm currently doing this inside WordPress


Solution

  • Use concat

    SELECT activity.item_id as course_id,
      postmeta.meta_value as quiz_data
    FROM wp_bp_activity as activity
    JOIN wp_postmeta as postmeta
      ON postmeta.post_id = activity.secondary_item_id
    WHERE activity.type = 'quiz_evaluated'
      AND concat('quiz_questions',activity.user_id )= postmeta.meta_key