MySQL: Left join and column with the same name in different tables

Question

Consider:

SELECT * FROM `product` left join category on product.category_id = category.id

This query works fine. But the problem is, both the product table and the category table have fields named "name" and "id". So when I fetch the result of this query, it gives me only one name and one id, but I want both id's and name's.

How can I do this without having to rename the fields? Is it possible to return with custom names such as product_name and category_name?

Answer 1

I had a similar problem working with MySQL in a Node.js project.

I found that if you still want to use select * instead of listing out all columns and using an alias, the following works:

SELECT *, category.id AS cId, category.name AS cName 
FROM product LEFT JOIN category ON product.category_id = category.id

In other words, just create aliases for the joined columns that have conflicting names. You don't need aliases on other columns or on the table names.