How to export the data of a Mysql table including the values of referenced ID's of another table

Question

//I have updated the question to hopefully clarify things better as I'm still at a loss. I got some very much appreciated help but I can't get it to work yet//

I'm still new at this, new here as well, so I hope I'll explain it clearly enough. If not, please let me know.

1. I have 2 tables Table 1 "students" , Table 2 'Mixed data"

2. Table 1 "students" has the columns "ID", "Name" and "Experience"

   In the column "Experience" the fields refer to data from Table 2 "Mixed Data"

3. I am able to export the data of the table "students" to CSV

I use for this export the following query

SELECT *
INTO OUTFILE '/tmp/results.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM students;

4. In the file "results.csv" the data of the ID's will be the ID's and not the real value/name

I like to be able to export the data into CSV and have the values and not the ID reference in the exported file.

Current export result

ID | Name | Experience
1 | Gary | 5
2 | Mary | 4

the number "4" and "5" in Experience refers to the id in table 2 "Mixed data"

Result I like to have in the export .csv"

ID | Name | Experience
1 | Gary | Super experienced
2 | Mary | Lots of experience

In this export of the CSV the experience field contains now the real values, being "Super experience", "Lots of experience"

Is it possible to export the data of a table including the values of referenced ID's of another table and if yes, how should I adjust my query.

Thanks so much for any help.

Answer 1

It is not a problem. Just add desired table to the SELECT statement, for example -

SELECT s.ID, s.Name, e.Experience FROM students s
JOIN experience e ON s.ID = e.ID
INTO OUTFILE '/tmp/results.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'