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?
You can add aliases to the fields:
SELECT
a.id,
a.name,
a.category_id,
b.id AS catId,
b.name AS catName
FROM
product AS a
LEFT JOIN
category AS b ON a.category_id = b.category.id
Use the "AS" keyword like:
SELECT product.id AS pid, category.id AS cid ... FROM `product` left join category on product.category_id = category.id
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.
AS aliases together with * is going to return the specified columns twice.Use aliases with the AS keyword:
SELECT p.id AS product_id, p.name AS product_name, c.id AS cat_id, c.name AS cat_name
FROM `product` AS p
LEFT JOIN category AS c ON p.category_id = c.id
SELECT p.*,c.* FROM product p LEFT JOIN category c on p.category_id = c.id;
Try this:
SELECT product.id AS productid,
category.id AS categoryid, ...
FROM `product` left join category
on product.category_id = category.id
No one has answered it so I am answering it. If Table 1 has lots of columns like 20 columns and Table 2 has lots of columns like 20 columns. Then it is very tiresome to write a query like table1.a, table1.b
mysqli fetch assoc will return the columns of the right table if the column has same name. Easiest solution I found was to use table1.* in the select
SELECT table1.* FROM table1 LEFT JOIN table2 on table1.id=table2.id
WHERE table1.branch LIKE '%' and table2.branch LIKE 'City'
This will result in columns only from table1, left join and table2 columns were just for where clause.
SELECT does not participate in join/where