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!
2
Answers
It sounds to me like you’re looking for a FULL OUTER JOIN. There’s a great article about it here.
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.