Complex SQL Query - just cannot get it to work


I am using two plugins, one called and the other is wpdatatables to try and achieve an SQL statement to display the data from the plugin employees scheduler using the plugin wpDataTables.

So Employee Scheduler records adhoc working shifts a user works and I want to use wpdatatables to generate a report table of all the hours worked but I am clearly not joining the tables correctly.

The structure is as follows;

Employee Scheduler records hours as a shift with the following data

Date recorded as meta_value under wp_postmeta.meta_key._wpaesm_date
Start Time recorded as meta_value under wp_postmeta.meta_key._wpaesm_starttime
End Time recorded as meta_value under wp_postmeta.meta_key._wpaesm_endtime
Shift ID is linked in another table by using the wp_postmeta.post_id

The shift type is defined within wp_terms We need it to identify two terms, one slug called 'extra' and one slug called 'worked' so these would be conditions to ensure that we only get records where the status is both extra and worked.

Now the term_id from wp_terms for each of those values is assigned to the post (shift) by using the wp_posts.ID in the wp_term_relationships table. The relationships are as follows;

wp_term_relationships.object_id = wp_posts.ID
wp_term_relationships.term_taxonomy_id = wp_terms.term_id

Now the result then needs to relate the shift to two additional values which identify the employee (user) and role carried out and these values are stored as follows. So first lets take a look at the role

wp_posts.post_title = Role
wp_posts.post_type will always need to = job

Then the wp_posts.ID for the above instance is related to the shift through wp_p2p table as follows

wp_posts.ID = wp_p2p.p2p_to WHERE the wp_p2p.p2p_from = wp_posts.ID (Shift ID) and the wp_p2p.p2p_type = shifts_to_jobs

The Employee is related to the shift through the wp_p2p table where the wp_p2p.p2p_from value matches the wp_posts.ID and the p2p_type = shifts_to_employees by returning the value in p2p_to and matching this to wp_users.ID

Hopefully the above has identified all the relationships etc between database tables.

What I would like to come up with is an SQL statement to return that data by visiting the abmin of the site, going to wpDataTables -> Add from Data Source -> choosing MySQL as the data type.

The table will hopefully return at least the following information (if it returns more its not a problem as any column can be hidden from displaying in the plugin)

All instances of hours worked recorded on the system where wp_terms of extra and worked exist as mentioned above

Display Name wp_users.display_name
Date wp_postmeta.meta_value WHERE meta_key = _wpaesm_date
Start Time wp_postmeta.meta_value WHERE meta_key = _wpaesm_starttime
End Time wp_postmeta.meta_value WHERE meta_key = _wpaesm_endtime
Role wp_posts.post_title WHERE post_type=job

Im online all of today and happy to reply pretty quickly if any clarification is required, I will reply quickly to help get this sorted.

Support Access is granted of course.

Im normally OK with SQL but when I have so many joins it is just confusing me. Thank you in advance.

Best Wishes