Question 166711 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Can wpDataTables Join WordPress & MYSQL Queries?

I am trying to wrap my head around this problem, but it has been a while since I have worked in mySQL and need a little help. I am working in WordPress and using WPDataTables plugin to help build this query. I have 2 forms, a self assessment from a resident, and then the user being given an assessment from a doctor.

I am using Gravity Forms for the form plugin and the way they store data in the DB makes this somewhat difficult, but I have that code from years ago and have that solved. I have created 2 view tables in mySQL and they display the correct data. For the resident query, I get the following result

traineeName procedureDate surgeryID traineeRating
Trainee 1 10-03-2020 id-001 3
Trainee 2 10-07-2020 id-004 2
Trainee 2 10-14-2020 id-014 3
Trainee 3 10-14-2020 id-016 3

For the doctor table I get the following result:

traineeName procedureDate surgeryID traineeRating
Trainee 1 10-03-2020 id-001 2
Trainee 3 10-10-2020 id-009 3

I would like to have the output combine these 2 queries and have it be:

traineeName procedureDate surgeryID traineeRating from Doctor traineeRating from Trainee
Trainee 1 10-03-2020 id-001 2 3
Trainee 2 10-07-2020 id-004 0 2
Trainee 3 10-10-2020 id-009 3 0
Trainee 2 10-14-2020 id-014 0 3
Trainee 3 10-14-2020 id-016 0 3 
SELECT t.*, d.* FROM BackbenchKidneyChartTrainee t
RIGHT JOIN BackbenchKidneyChartDoctor d ON t.traineeName = d.traineeName
WHERE d.surgeryID = t.surgeryID;

The above query produces this result:

traineeName procedureDate surgeryID traineeRating traineeName procedureDate surgeryID doctorRating
TraineeName1 2021-03-13 HFHS-00483 3 TraineeName1 2021-03-13 HFHS-00483 2

Any help here would be much appreciated. Thank you in advance!

Tags:

2

Answers


  1. 0

    It sounds to me like you’re looking for a FULL OUTER JOIN. There’s a great article about it here.

    SELECT
    t1.traineeName, t1.procedureDate, t1.surgeryID, t1.traineeRating AS traineeRatingFromDoctor, t2.traineeRating AS traineeRatingFromTrainee
FROM
    BackbenchKidneyChartDoctor t1
        FULL OUTER JOIN
    BackbenchKidneyChartTrainee t2 ON t1.traineeName = t2.traineeName
WHERE
    t1.surgeryID = t2.surgeryID;
    Login or Signup to reply.
  2. 0

    Just a thought…If you can’t figure out the join in Mysql, I would recommend spending a little money and purchasing the Bulk Import Entries plugin by Gravityview:
    https://gravityview.co/pricing/ (look for the Gravity Forms Add-Ons)

    This way you can have all the entries combined in one form and display in WPdatatables a bit easier.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search