Get CSV from query which involves multiple tables

Question

I'm trying to make a query to extract elements from 2 tables, which are linked via another table.

So I have 3 tables:

authors
 - id, name, book
category
 - id, name, description
category-author
 - id, idauthor, idcategory

Now I want to make a query to make the following output:

row: authors.id, authors.name, authors.book, category.name

I don't know what category's are linked using the 2 tables only, so I need to use the last one, the category-author table. The id's of both the author and the category are linked via that table.

I got the following query:

SELECT authors.id, authors.name, authors.book, category.name FROM category, author LEFT JOIN SELECT ??

I'm stuck at the remaining part of the query.

Also when I have this query, can I just extract a CSV with phpmyadmin?

Answer 1

You can get related information from different tables using table joins. Relations between tables should be specified using foreign keys (i.e. the column idcategory from category-author is presumably a foreign key that refers to primary key column category.id). In a join clause, you merely specify which tables are to be joined and on what column:

SELECT table1.col1, table2.col2 FROM table1 JOIN table2 ON table1.pkCol = table2.fkCol

This means you can't specify any SELECT or FROM clauses within a JOIN clause. The columns you wish to select from joined tables are all specified in the initial SELECT statement, and you only specify one table in the FROM clause, from which you subsequently perform the table joins. In your case, I think this should get you started:

SELECT authors.id, authors.name, authors.book, category.name 
FROM category 
LEFT JOIN category-author ON category-author.idcategory = category.id
LEFT JOIN authors ON authors.id = category-author.idauthor

I'm not sure how familiar you are with foreign keys, primary keys and table joins, so I won't elaborate any more on this. I think specifying multiple tables in a FROM clause is bad practice, even if your database system still supports it (Related question).

From then on, you can easily export the results from within PhpMyAdmin, as there is an export button for every table overview, including query results.