William William - 5 months ago 9
SQL Question

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
(wich 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

Answer

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
Comments