MYSQL Query with wpDataTables with Join

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!

https://stackoverflow.com/questions/66619913/mysql-query-with-wpdatatables-with-join March 14, 2021 at 08:29AM