Can't figure out sql join-CodePudding

I'm using nextcloud to track data via the forms app, the table oc_forms_v2_submissions contains the entries:

SELECT * FROM `oc_forms_v2_submissions` WHERE `form_id` = 3;
 ---- --------- -------------------------------------------- ------------ 
| id | form_id | user_id                                    | timestamp  |
 ---- --------- -------------------------------------------- ------------ 
|  8 |       3 | anon-user-96684f301d22e7be44f07780a9bffe06 | 1663789158 |
|  9 |       3 | anon-user-a1eaa4f939b59e00b403c046410788aa | 1663835954 |
| 10 |       3 | anon-user-440d0dbe9c107492b6ec1a06d98004a8 | 1663942458 |
 ---- --------- -------------------------------------------- ------------

the second table is oc_forms_v2_answers

SELECT * FROM `oc_forms_v2_answers`;
 ---- --------------- ------------- ----------------------- 
| id | submission_id | question_id | text                  |
 ---- --------------- ------------- ----------------------- 
| 10 |             8 |           7 | foo                   |
| 11 |             9 |           7 | bar                   |
| 12 |            10 |           7 | foo                   |
 ---- --------------- ------------- -----------------------

So basically i need to the take all the id entries from table submissions and match them with submission_id from answers and I want to have the data from the text column.

SELECT oc_forms_v2_submissions.id as submission_id
FROM `oc_forms_v2_submissions` 
RIGHT  JOIN `oc_forms_v2_answers` ON submission_id=oc_forms_v2_answers.submission_id;

This is all i could come up with so far but that returns only the submission_id field and everything triplicated :-D

 --------------- 
| submission_id |
 --------------- 
|             8 |
|             8 |
|             8 |
|             9 |
|             9 |
|             9 |
|            10 |
|            10 |
|            10 |
 ---------------

Edit:

The updated query still does not get me the field from oc_forms_answers:

SELECT oc_forms_v2_submissions.id as submission_id
FROM `oc_forms_v2_submissions`
RIGHT JOIN `oc_forms_v2_answers` ON oc_forms_v2_submissions.id=oc_forms_v2_answers.submission_id where form_id="3";

CodePudding user response：

that is because you are comparing to identical columns, you need in the ON Clause, the link columns of both tables

Also you can use alias, to reduce the typing time

The RIGHT JOIN would also combine all answers with thes ubmission, but you you will never have more submission as answer, so a LEFT JOIN would gove ou all submissions even if there is no answer

SELECT oc_forms_v2_submissions.id as submission_id
FROM `oc_forms_v2_submissions` 
LEFTJOIN `oc_forms_v2_answers` ON oc_forms_v2_submissions.id=oc_forms_v2_answers.submission_id;

CodePudding user response：

This should do the trick (just update the correct naming of columns and tables)

SELECT s.id as submission_id, a.txt FROM submissions s 
LEFT JOIN answers a 
ON s.id=a.submission_id;

You can check this here in db-fiddle. I've used your info for creating a DB, so WHERE clause is missing but all the rest should give you results you're after.