MYSQL Query with wpDataTables with Join

Question

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!

DeFeNdog · Answer 1 · 2021-03-14T16:26:49.460

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;

edited Mar 14 '21 at 16:26

answered Mar 14 '21 at 01:14

DeFeNdog

1,156
1
12
25

Thank you so much. This gives me the query in one row, but does not give me the form submissions where the Doctor has evaluated the trainee, but the trainee has not evaluated themself yet. Also, where the trainee has evaluated themselves, but the doctor has not evaluated them. How could I include those rows? – hosker Mar 14 '21 at 01:29
https://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram May help – DeFeNdog Mar 14 '21 at 02:17
@hosker I updated my answer to FULL OUTER JOIN. Hope it helps. – DeFeNdog Mar 17 '21 at 03:13

score 0 · Answer 2 · answered Mar 14 '21 at 04:06

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.

MYSQL Query with wpDataTables with Join

2 Answers2