Complex Transformation & Joins Not Working

Question

I'm getting stumped on a complex MySQL Query. I've tried joins, and I've tried using where X=Y clauses to merge multiple WordPress tables together.

What I am attempting to do is export WordPress users, including specific user meta data merged with activity information from another table.

If you are not familiar with WordPress's table structure, here is a summary of the relevant built in tables

_usermeta

umeta_id	user_id	meta_key	meta_value
1	1	first_name	Kevin
2	1	last_name	Gee
3	1	ID_number	123456

_users

ID	user_login	user_pass	...	user_email	...
1	kevin	...	...	kevin@example.com	...

_posts

ID	...	post_title	...
1	...	Course Name	...

The plug in we are using is Learn Dash, and we are trying to extract activity information from the table below

_learndash_user_activity

activity_id	user_id	post_id	course_id	activity_type	activity_status	activity_started	activity_completed	activity_updated
34	5	252	252	course	1	1612283718	1614975038	1614975038
35	5	271	252	lesson	1	1612283818	1614976038	1614975034

To transform the meta data in to something useful, I know I can do something like this, which gets me the name, ID info and email address of the users

SELECT _usermeta.user_id,
MAX(CASE WHEN _usermeta.meta_key = "first_name" THEN _usermeta.meta_value END) "First Name",
MAX(CASE WHEN _usermeta.meta_key = "last_name" THEN _usermeta.meta_value END) "Last Name",
MAX(CASE WHEN _usermeta.meta_key = "ID_number" THEN _usermeta.meta_value END) "ID Number",
_users.user_email as "Email Address"
FROM _usermeta
JOIN _users on _usermeta.user_id = _users.ID
GROUP BY _usermeta.user_id

And, I can grab the completed course information like this

SELECT  
_learndash_user_activity.user_id as "User ID", 
_posts.post_title as "Course Name",
FROM_UNIXTIME(_learndash_user_activity.activity_completed) as "Completed Timestamp"      
FROM _learndash_user_activity
JOIN _posts on _learndash_user_activity.post_id = _posts.ID
WHERE 
_learndash_user_activity.activity_type = 'course' AND
_learndash_user_activity.activity_status = 1 AND
_learndash_user_activity.activity_completed IS NOT NULL

Which returns a table of all completed courses with the user ID's

But, when I try to merge these two queries together on another join for user_id to bring everything together, I either timeout the query as it never ends, or I get a garbage output of never ending data.

My intent is to generate a table like this

_output

_usermeta.user_id	_usermeta.meta_key(first_name)	_usermeta.meta_key(last_name)	_users.user_email	_usermeta.meta_key(id_number)	_posts.post_title	_learndash_user_activity.activity_completed
1	Kevin	Gee	email_address	123456	Course 101	January 31, 2021
2	Test	User	email_address	654321	Course 101	February 1, 2021
1	Kevin	Gee	email_address	123456	Course 102	February 6, 2021

What is the best way to merge these two queries together to return useful information similar to my table above. Is this doable with a single query?

Any advice you can provide is appreciated!

Answer 1

you need to join bioth queries

SELECT 
    t1.user_id,
    t1.`First Name`,
    t1.`Last Name`,
    t1.`ID Number`,
    t2.`Course Name`,
    t2.`Completed Timestamp`
FROM
    (SELECT _usermeta.user_id,
    MAX(CASE WHEN _usermeta.meta_key = "first_name" THEN _usermeta.meta_value END) "First Name",
    MAX(CASE WHEN _usermeta.meta_key = "last_name" THEN _usermeta.meta_value END) "Last Name",
    MAX(CASE WHEN _usermeta.meta_key = "ID_number" THEN _usermeta.meta_value END) "ID Number",
    _users.user_email as "Email Address"
    FROM _usermeta
    JOIN _users on _usermeta.user_id = _users.ID
    GROUP BY _usermeta.user_id) t1
JOIN
    (SELECT  
    _learndash_user_activity.user_id as "User ID", 
    _posts.post_title as "Course Name",
    FROM_UNIXTIME(_learndash_user_activity.activity_completed) as "Completed Timestamp"      
    FROM _learndash_user_activity
    JOIN _posts on _learndash_user_activity.post_id = _posts.ID
    WHERE 
    _learndash_user_activity.activity_type = 'course' AND
    _learndash_user_activity.activity_status = 1 AND
    _learndash_user_activity.activity_completed IS NOT NULL) te ON t1.user_id = t2.user_id