CODEWITHSUNDEEP
phpmysqlwordpress
William
William

Reputation: 1030

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

Upvotes: 1

Views: 40

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

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

Upvotes: 2

Related Questions